c_bpartner_contact_check.sql

来自「Java写的ERP系统」· SQL 代码 · 共 38 行

/** Add Internal Contacts
**/
DECLARE
	CURSOR CUR_NoContact IS
		SELECT C_BPartner_ID, Name, AD_Client_ID, AD_Org_ID
		FROM C_BPartner bp
		WHERE EXISTS (SELECT * FROM AD_User u WHERE bp.C_BPartner_ID=u.C_BPartner_ID)
		  AND NOT EXISTS (SELECT * FROM C_BPartner_Contact bpc WHERE bp.C_BPartner_ID=bpc.C_BPartner_ID);
	v_C_BPartner_Contact_ID	NUMBER(10);
BEGIN
	FOR c IN CUR_NoContact LOOP
		AD_Sequence_Next ('C_BPartner_Contact', c.AD_Client_ID, v_C_BPartner_Contact_ID);
		INSERT INTO C_BPartner_Contact
		  (C_BPartner_Contact_ID,
		  AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
		  C_BPartner_ID, Name)
		VALUES
		  (v_C_BPartner_Contact_ID,
		  c.AD_Client_ID,c.AD_Org_ID,'Y',SysDate,0,SysDate,0,
		  c.C_BPartner_ID, c.Name);
		DBMS_OUTPUT.PUT_LINE('C_BPartner_Contact=' || v_C_BPartner_Contact_ID);
	END LOOP;
	COMMIT;
END;
/
UPDATE C_BPartner_Contact
  SET IsOnline='Y'
WHERE C_BPartner_ID IN (SELECT C_BPartner_ID FROM AD_User)
  AND IsOnline='N'
/
UPDATE C_BPartner_Contact bpc
  SET EMail = (SELECT Email FROM AD_User u WHERE bpc.C_BPartner_ID=u.C_BPartner_ID)
WHERE C_BPartner_ID IN (SELECT C_BPartner_ID FROM AD_User)
  AND EMail IS NULL
/
COMMIT
/