APPS.XNB_CMN_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 12

    g_item_update_txn_subtype		CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'IO';

Line: 25

/*PROCEDURE check_acct_update_publish
(
		 		 itemtype  	IN VARCHAR2,
				 itemkey 	IN VARCHAR2,
				 actid 		IN NUMBER,
				 funcmode 	IN VARCHAR2,
				 resultout 	OUT NOCOPY VARCHAR2
)
AS
	l_transaction_type	        VARCHAR2(15);

Line: 59

     XNB_DEBUG.log('check_acct_update_publish',l_event_name);

Line: 61

	IF l_event_name = 'oracle.apps.xnb.account.update' THEN

		l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, 'XNB_ACCOUNT');

Line: 65

		XNB_DEBUG.log('check_acct_update_publish',l_num);

Line: 83

END check_acct_update_publish; */

Line: 108

	l_transaction_subtype := g_item_update_txn_subtype;

Line: 118

            SELECT  party_id,
                    party_site_id
            INTO    l_party_id,
                    l_party_site_id
            FROM    ecx_oag_controlarea_tp_v
            WHERE   transaction_type = l_transaction_type
            AND     transaction_subtype = l_transaction_subtype
            AND     party_type = l_party_type;

Line: 207

	--else Collaboration exists so Update
	---------------------------------------------------------------------------------------

	l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, l_transaction_type, l_transaction_subtype);

Line: 218

		--Update the MESSAGE_TEXT to reflect the Update of Collaboration
		--
		---------------------------------------------------------------------------------------

		l_message_text := l_transaction_type||l_transaction_subtype||l_party_id||l_doc_no||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');

Line: 233

						'LAST_UPDATE_DATE',
						sysdate);

Line: 249

PROCEDURE set_acct_update_attributes (
				itemtype  	IN VARCHAR2,
		 		itemkey 	IN VARCHAR2,
		 		actid 		IN NUMBER,
		 		funcmode 	IN VARCHAR2,
		 		resultout 	OUT NOCOPY VARCHAR2
		 	     )
AS

	l_transaction_type 	    VARCHAR2(15) ;

Line: 310

	SELECT	cust_account_id
	INTO	l_cust_ac_id
	FROM	hz_cust_accounts
	WHERE   account_number = l_doc_no;

Line: 315

	SELECT	cust_account_id
	INTO	l_temp
	FROM	xnb_primary_bill_to_addr_v
	WHERE	cust_account_id = l_cust_ac_id
	AND	org_id = l_org_id;

Line: 338

     SELECT     party_id,
                party_site_id
        INTO    l_party_id,
                l_party_site_id
        FROM    ecx_oag_controlarea_tp_v
        WHERE   transaction_type = l_transaction_type
        AND     transaction_subtype = l_transaction_subtype
        AND     party_type = l_party_type;

Line: 409

	    l_event_name := 'oracle.apps.xnb.account.update';

Line: 430

END set_acct_update_attributes;

Line: 495

	SELECT	cust_account_id
	INTO	l_cust_ac_id
	FROM	hz_cust_accounts
	WHERE   account_number = l_doc_no;

Line: 500

	SELECT	cust_account_id
	INTO	l_temp
	FROM	xnb_primary_bill_to_addr_v
	WHERE	cust_account_id = l_cust_ac_id
	AND	org_id = l_org_id;

Line: 523

     SELECT     party_id,
                party_site_id
        INTO    l_party_id,
                l_party_site_id
        FROM    ecx_oag_controlarea_tp_v
        WHERE   transaction_type = l_transaction_type
        AND     transaction_subtype = l_transaction_subtype
        AND     party_type = l_party_type;

Line: 615

	--else Collaboration exists so Update
	---------------------------------------------------------------------------------------

	l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, 'XNB_ACCOUNT');

Line: 625

		--Update the MESSAGE_TEXT to reflect the Update of Collaboration
		--
		---------------------------------------------------------------------------------------

		   l_message_text := l_transaction_type||l_transaction_subtype||l_party_id||l_doc_no||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');

Line: 640

						'LAST_UPDATE_DATE',
						SYSDATE);

Line: 688

             SELECT     party_id,
                        party_site_id
             INTO       l_party_id,
                        l_party_site_id
             FROM        ecx_oag_controlarea_tp_v
             WHERE       transaction_type = l_transaction_type
             AND         transaction_subtype = l_transaction_subtype
             AND         party_type = l_party_type;

Line: 774

		SELECT		ohdr.order_number
		INTO		l_order_num
		FROM		oe_order_headers_all ohdr,
			        oe_order_lines_all oline
		WHERE		ohdr.header_id = oline.header_id
		AND		oline.line_id = l_doc_no;

Line: 816

	--else Collaboration exists so Update
	---------------------------------------------------------------------------------------

/*	l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, l_transaction_type, l_transaction_subtype);

Line: 874

             SELECT     party_id,
                        party_site_id
             INTO       l_party_id,
                        l_party_site_id
             FROM        ecx_oag_controlarea_tp_v
             WHERE       transaction_type = l_transaction_type
             AND         transaction_subtype = l_transaction_subtype
             AND         party_type = l_party_type;

Line: 976

        SELECT  msib.invoiceable_item_flag
        INTO    l_invoiceable_flag
        FROM    mtl_system_items_b msib,
                csi_item_instances cii
        WHERE   cii.instance_id = l_instance_id
        AND     msib.inventory_item_id = cii.inventory_item_id
        AND     msib.organization_id = cii.inv_master_organization_id;

Line: 1026

        l_app_ref_id                VARCHAR2(100);      --for the Account Sub Flow. Application Ref ID for CLN update

Line: 1044

        SELECT  account_number
        INTO    l_account_number
        FROM    hz_cust_accounts
        WHERE   cust_account_id = l_account_id;

Line: 1064

        SELECT  inv_master_organization_id
        INTO    l_inv_org_id
        FROM    csi_item_instances
        WHERE   instance_id = l_instance_id;

Line: 1193

		SELECT		ohdr.order_number
		INTO		l_order_num
		FROM		oe_order_headers_all ohdr,
			        oe_order_lines_all oline
		WHERE		ohdr.header_id = oline.header_id
		AND		oline.line_id = l_line_id;

Line: 1213

	SELECT org_id into l_org_id from oe_order_lines_all
	WHERE line_id = l_line_id;

Line: 1260

  SELECT	accounts.account_number
  FROM		xnb_salesorder_accounts_v accounts
  WHERE		accounts.order_number = pl_order_number
  AND		accounts.org_id = pl_org_id;

Line: 1281

	-- SELECT org_id INTO l_org_id FROM oe_order_headers_all
	-- WHERE order_number = l_order_no;

Line: 1352

SELECT DISTINCT event_name FROM xnb_subscribed_events
WHERE entity_type = 'ACCOUNT_UPDATE';

Line: 1370

            publish_account_update(l_event_name, p_event);

Line: 1399

PROCEDURE publish_account_update(l_event_name IN VARCHAR2,
                                p_event              IN OUT NOCOPY WF_EVENT_T)
AS

    l_param_value 		VARCHAR2(60);

Line: 1417

/* 30-May-2006  pselvam   ST1 Bug Fix 5254717 - Acct Update Org Id issue */
    l_user_id                 NUMBER;

Line: 1429

/*30-May-2006  pselvam   ST1 Bug Fix 5254717 - Acct Update Org Id issue*/
--fnd_profile.GET( 'ORG_ID', l_org_id );

Line: 1445

IF l_event_name = 'oracle.apps.ar.hz.CustAccount.update' THEN

    l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_ID');

Line: 1453

		SELECT  ACCOUNT_NUMBER
		INTO    l_account_number
		FROM    HZ_CUST_ACCOUNTS
		WHERE   CUST_ACCOUNT_ID = l_param_value;

Line: 1458

		XNB_DEBUG.log('oracle.apps.ar.hz.CustAccount.update',l_account_number);

Line: 1463

			WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1465

			XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAccount.update');

Line: 1472

		raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1476

ELSIF l_event_name = 'oracle.apps.ar.hz.BillingPreference.create' OR l_event_name = 'oracle.apps.ar.hz.BillingPreference.update' THEN

    l_param_value := p_event.GetValueForParameter('BILLING_PREFERENCES_ID');

Line: 1483

        SELECT      account_number
        INTO        l_account_number
        FROM        hz_billing_preferences bill_pref,
                    hz_cust_accounts acc
        WHERE       bill_pref.cust_account_id = acc.cust_account_id
        AND         bill_pref.billing_preferences_id = l_param_value;

Line: 1495

                WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1497

                XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR BillingPreference');

Line: 1504

		raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1507

ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctRelate.create' OR l_event_name = 'oracle.apps.ar.hz.CustAcctRelate.update' THEN

    l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_ID');

Line: 1515

        SELECT              distinct ACCT.ACCOUNT_NUMBER
        INTO                l_account_number
        FROM                HZ_CUST_ACCOUNTS        ACCT,
                            HZ_CUST_ACCT_RELATE_ALL ACCT_REL
        WHERE               ACCT.CUST_ACCOUNT_ID = l_param_value
        AND                 ACCT.CUST_ACCOUNT_ID = ACCT_REL.CUST_ACCOUNT_ID;

Line: 1530

                WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1532

                XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctRelate');

Line: 1539

		raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1543

ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.create' OR l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.update' THEN
    l_param_value := p_event.GetValueForParameter('SITE_USE_ID');

Line: 1551

        SELECT 	 	   site_use_code,
			   primary_flag
        INTO		   l_site_use_code,
                           l_flag
        FROM 		   hz_cust_site_uses_all
        WHERE 		   site_use_id = l_param_value;

Line: 1561

            WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1563

                XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse');

Line: 1578

                    SELECT 	 	   b.account_number
 	            INTO		   l_account_number
                    FROM		   hz_cust_site_uses_all p,
                    			   hz_cust_acct_sites_all a,
            	          		   hz_cust_accounts b
                    WHERE		   p.site_use_id = l_param_value
                    AND 		   a.cust_acct_site_id = p.cust_acct_site_id
                    AND  		   a.cust_account_id = b.cust_account_id;

Line: 1592

                        WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1594

                        XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse.create');

Line: 1601

				raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1606

        ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.update' THEN

            IF  l_site_use_code = 'BILL_TO' THEN

                BEGIN

			/* R12 MOAC UPTAKE :	ksrikant*/

                    SELECT 	 	   b.account_number
 	            INTO		   l_account_number
                    FROM		   hz_cust_site_uses_all p,
                    			   hz_cust_acct_sites_all a,
            	          		   hz_cust_accounts b
                    WHERE		   p.site_use_id = l_param_value
                    AND 		   a.cust_acct_site_id = p.cust_acct_site_id
                    AND  		   a.cust_account_id = b.cust_account_id;

Line: 1623

                    XNB_DEBUG.log('oracle.apps.ar.hz.CustAcctSiteUse.update',l_account_number);

Line: 1628

                        WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1630

                        XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse.update');

Line: 1637

			raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1644

ELSIF l_event_name = 'oracle.apps.ar.hz.CustProfileAmt.create' OR l_event_name = 'oracle.apps.ar.hz.CustProfileAmt.update' THEN

    l_param_value := p_event.GetValueForParameter('CUST_ACCT_PROFILE_AMT_ID');

Line: 1652

        SELECT      account_number,
	            pfl.site_use_id
        INTO        l_account_number,
	            l_site_use_id
        FROM        HZ_CUST_PROFILE_AMTS pfl_amnts,
	            HZ_CUSTOMER_PROFILES pfl,
                    hz_cust_accounts acc
        WHERE       pfl_amnts.cust_account_id = acc.cust_account_id
	AND         pfl.cust_account_profile_id = pfl_amnts.cust_account_profile_id
	AND         pfl_amnts.CUST_ACCT_PROFILE_AMT_ID = l_param_value;

Line: 1668

                WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1670

                XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustProfileAmt');

Line: 1676

	    XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR CustProfileAmt_'||l_num);

Line: 1679

			raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1680

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR CustProfileAmt');

Line: 1685

ELSIF l_event_name = 'oracle.apps.ar.hz.Person.update' OR l_event_name = 'oracle.apps.ar.hz.Organization.update' THEN

	l_param_value := p_event.GetValueForParameter('PARTY_ID');

Line: 1692

		SELECT			account_number
		BULK COLLECT INTO	l_acc_num
		FROM			hz_cust_accounts
		WHERE			party_id = l_param_value;

Line: 1701

			WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1703

	                XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR Party Update');

Line: 1707

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);

Line: 1711

	XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Before FOR Loop of Party');

Line: 1715

		XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Inside FOR Loop of Party');

Line: 1717

		    XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Party_'||l_acc_num(i)||'_'||l_num);

Line: 1720

			raise_acctupdate_event(l_acc_num(i), l_org_id, l_event_name, l_param_value);

Line: 1721

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Party Account Num_'||l_acc_num(i));

Line: 1726

ELSIF l_event_name = 'oracle.apps.ar.hz.CustomerProfile.update' THEN

	l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_PROFILE_ID');

Line: 1733

		SELECT 	 b.account_number,
		         a.site_use_id
		INTO   	 l_account_number,
		         l_site_use_id
		FROM 	 hz_customer_profiles a,
		 	 hz_cust_accounts b
		WHERE 	 a.cust_account_profile_id = l_param_value
		AND 	 a.cust_account_id = b.cust_account_id;

Line: 1747

			WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1749

	                XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','Check the Cust_Account_Id for the Updated Customer Profile');

Line: 1753

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);

Line: 1759

		XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Credit CustomerProfile_'||l_num);

Line: 1762

			raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);

Line: 1763

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Credit CustomerProfile');

Line: 1767

ELSIF l_event_name = 'oracle.apps.ar.hz.ContactPoint.update' THEN

	l_param_value := p_event.GetValueForParameter('CONTACT_POINT_ID');

Line: 1774

		SELECT 	 owner_table_name
		INTO   	 l_table_name
		FROM 	 hz_contact_points
		WHERE	 contact_point_id = l_param_value;

Line: 1782

			WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1784

	                XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','Check the Owner Table Name in HZ_CONTACT_POINTS');

Line: 1788

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);

Line: 1794

	XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', 'Inside IF l_table_name = hz_parties');

Line: 1798

			SELECT 			c.account_number
			BULK COLLECT INTO	l_acc_num
			FROM 			hz_contact_points a,
						hz_parties b,
						hz_cust_accounts c
			WHERE 			a.contact_point_id = l_param_value
			AND 			a.owner_table_id = b.party_id
			AND 			b.party_id = c.party_id;

Line: 1810

				WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());

Line: 1812

				XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR Contact Point Update');

Line: 1816

				XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);

Line: 1820

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Before FOR Loop of Contact Point Update');

Line: 1824

			XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Inside FOR Loop of Contact Point Update');

Line: 1827

			    XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Contact Point_'||l_acc_num(i)||'_'||l_num);

Line: 1830

				raise_acctupdate_event(l_acc_num(i), l_org_id, l_event_name, l_param_value);

Line: 1831

				XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Contact Point_'||l_acc_num(i));

Line: 1840

END publish_account_update;

Line: 1845

PROCEDURE raise_acctupdate_event(
					p_account_number IN VARCHAR2,
					p_org_id	 IN NUMBER,
					p_event_name	 IN VARCHAR2,
					p_param_value	 IN VARCHAR2)
AS

	    l_parameter_list wf_parameter_list_t := wf_parameter_list_t();

Line: 1861

	--raise the business event to publish the account update message
	--
	------------------------------------------------------------------------------------

	l_key := 'XNB:'||'ACCOUNT:'||p_account_number||':'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');

Line: 1871

	XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','Org_id before Raising Event'||p_org_id);

Line: 1885

	wf_event.raise(	p_event_name => 'oracle.apps.xnb.account.update',
			p_event_key => l_key,
			p_parameters => l_parameter_list);

Line: 1897

          XNB_DEBUG.log('XNB_CMN_PVT_TEMP.RAISE_ACCTUPDATE_EVENT',l_err_name||' : '||l_err_message);

Line: 1900

END RAISE_ACCTUPDATE_EVENT;

Line: 1934

             SELECT     party_id,
                        party_site_id
             INTO       l_party_id,
                        l_party_site_id
             FROM        ecx_oag_controlarea_tp_v
             WHERE       transaction_type = l_transaction_type
             AND         transaction_subtype = l_transaction_subtype
             AND         party_type = l_party_type;

Line: 2049

		SELECT		count(line.line_id)
		INTO		l_num
		FROM		oe_order_headers_all  head,
				oe_order_lines_all    line,
				mtl_system_items_vl   item
		WHERE		head.order_number = l_order_number
		AND   		head.header_id = line.header_id
		AND		line.inventory_item_id = item.inventory_item_id
		AND		item.organization_id  =   line.ship_from_org_id
	        AND		item.invoiceable_item_flag = 'N';

Line: 2075

			SELECT		org_id
			INTO		l_org_id
			FROM		oe_order_headers_all
			WHERE		order_number = l_order_number;