DBA Data[Home] [Help]

APPS.AR_EXCHANGE_INTERFACE_PKG SQL Statements

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

Line: 37

last_update_date	DATE,
customer_name		VARCHAR2(50),
party_number		VARCHAR2(30),
address1		varchar2(240),
address2		varchar2(240),
address3		varchar2(240),
address4		varchar2(240),
city			varchar2(60),
county			varchar2(60),
state			varchar2(60),
country			varchar2(60),
postal_plus4_code	varchar2(10),
bank_name		varchar2(30),
account_name		varchar2(80),
account_number		varchar2(80),
account_currency	varchar2(15),
account_description	varchar2(240),
account_exp_date	DATE,
payment_method_code	varchar2(30),
insert_update_flag	varchar2(10),
org_id			number(15)
);
Line: 91

	select  meaning
	from    fnd_lookup_values
	where   lookup_type = p_lookup_type
	and 	lookup_code = p_lookup_code
	and 	language = userenv('LANG');
Line: 151

 Transaction to insert into all interface records relating
 to one exchange billing customer record.
*/
PROCEDURE transfer_customer_record (
	cust_rec 	IN OexCustRecTyp,
	p_transfer_flag OUT NOCOPY varchar2
	) IS

TYPE OexContactRecTyp is RECORD (
person_title		varchar2(255),
person_first_name	varchar2(255),
person_last_name	varchar2(255),
contact_point_id	NUMBER(15),
contact_point_type	varchar2(255),
phone_line_type		varchar2(255),
phone_area_code		varchar2(255),
phone_number		varchar2(255),
phone_extension		varchar2(255),
email_address		varchar2(2000)
);
Line: 185

						--  'U'(update), else 'I'(insert)
l_payment_method_name    	varchar2(240);
Line: 198

INSERT INTO ra_customers_interface_all (
org_id, orig_system_customer_ref, orig_system_address_ref, insert_update_flag,
customer_name, customer_number, address1, address2, address3, address4,
city, county, state, country, postal_code,
customer_prospect_code, customer_status, customer_type,
primary_site_use_flag, site_use_code,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8, :9,
:10, :11, :12, :13, :14,
:15, :16, :17,
:18, :19,
:20, :21, :22, :23, :24
FROM DUAL ';
Line: 216

INSERT INTO ra_customer_profiles_int_all (
org_id, orig_system_customer_ref, insert_update_flag,
customer_profile_class_name, credit_hold,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5,
:6, :7, :8, :9
FROM DUAL';
Line: 228

INSERT INTO ra_cust_pay_method_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
payment_method_name, start_date, primary_flag,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5, :6,
:7, :8, :9, :10
FROM DUAL';
Line: 240

INSERT INTO ra_customer_banks_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
bank_account_num, bank_account_currency_code, bank_account_inactive_date,
bank_account_name,
bank_name,
bank_branch_name,
start_date, primary_flag,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5, :6,
:7,
decode(:8, :9, :10, :11),
decode(:12, :13, :14, :15),
:16, :17, :18,
:19, :20, :21
FROM DUAL';
Line: 260

INSERT INTO ra_contact_phones_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
orig_system_contact_ref,
contact_first_name, contact_last_name, contact_title,
insert_update_flag, email_address,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4,
:5, :6, :7,
:8, :9,
:10, :11, :12, :13
FROM DUAL';
Line: 276

INSERT INTO ra_contact_phones_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
orig_system_contact_ref, orig_system_telephone_ref,
contact_first_name, contact_last_name, contact_title,
insert_update_flag, telephone_type, telephone,
telephone_area_code, telephone_extension, email_address,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5,
:6, :7, :8,
:9,
decode(:10, :11, :12, :13),
decode(:14, :15, :16, :17),
:18, :19, :20,
:21, :22, :23, :24
FROM DUAL';
Line: 305

 select
 hpcont.person_pre_name_adjunct	person_title,
 hpcont.person_first_name	person_first_name,
 hpcont.person_last_name		person_last_name,
 hcp1.contact_point_id		contact_point_id,
 hcp1.contact_point_type		contact_point_type,
 hcp1.phone_line_type		phone_line_type,
 hcp1.phone_area_code		phone_area_code,
 hcp1.phone_number		phone_number,
 hcp1.phone_extension		phone_extension,
 hcp1.email_address		email_address
 from
 hz_parties'||g_oexdblink||' hpcont,
 hz_contact_points'||g_oexdblink||' hcp1
 where 	hpcont.party_id 	= :1
 and	hcp1.owner_table_id 	= hpcont.party_id
 and	hcp1.owner_table_name 	= ''HZ_PARTIES''
';
Line: 325

SELECT  rm.name
FROM 	hz_cust_accounts'||g_oexdblink||' hca,
	ar_receipt_methods rm,
	ra_cust_receipt_methods rcrm
WHERE 	hca.orig_system_reference = :1
AND	rm.name = :2
AND 	rcrm.customer_id = hca.cust_account_id
AND     rcrm.receipt_method_id = rm.receipt_method_id
AND	sysdate <= nvl(rcrm.end_date,sysdate)
';
Line: 337

UPDATE  pom_billing_customers'||g_oexdblink||'
 SET     ar_transfer_flag = null,
    insert_update_flag = null,
    last_update_date = sysdate
WHERE   billing_customer_id = :1
';
Line: 345

UPDATE  pom_billing_customers'||g_oexdblink||'
 SET     ar_transfer_flag = ''E'',
	 request_id = null,
    	 last_update_date = sysdate
WHERE   billing_customer_id = :1
';
Line: 368

		if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
			l_primary_su_flag := 'Y';
Line: 382

			cust_rec.insert_update_flag,
			cust_rec.customer_name,
			cust_rec.party_number,
			nvl(cust_rec.address1,l_a_null),
			nvl(cust_rec.address2,l_a_null),
			nvl(cust_rec.address3,l_a_null),
			nvl(cust_rec.address4,l_a_null),
			nvl(cust_rec.city,l_a_null),
			nvl(cust_rec.county,l_a_null),
			nvl(cust_rec.state,l_a_null),
			nvl(cust_rec.country,l_a_null),
			nvl(cust_rec.postal_plus4_code,l_a_null),
			'CUSTOMER',
			'A',
			'R',
		 	nvl(l_primary_su_flag,l_a_null),
			nvl(l_site_use_code,l_a_null),
			-1,
			sysdate,
			-1,
			sysdate	;
Line: 403

		print_debug(0,'-inserted customer interface record.');
Line: 406

		if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
			EXECUTE IMMEDIATE l_sql_profiles_interface
			USING
				cust_rec.org_id,
				cust_rec.orig_sys_cust_ref,
				'I',
				'DEFAULT',
				'N',
				-1,
				sysdate,
				-1,
				sysdate;
Line: 419

		 print_debug(0,'-inserted profile interface record.');
Line: 436

			    -- no payment method exists for sysdate, insert one
			    l_pm_ins_upd_flag := 'I';
Line: 459

		    print_debug(0,'-inserted payment method interface record.');
Line: 462

		    -- Insert bank account information only for automatic payment methods
		    -- like credit_card  and eft. For bill_me (manual payment), we do not
		    -- capture account number during registration.
		    --
		    IF ( (cust_rec.payment_method_code <> 'BILL_ME') AND
		         (cust_rec.account_number IS NOT NULL) ) THEN

			l_account_number := null;
Line: 470

			print_debug(0,'-inserting bank interface record.');
Line: 500

			print_debug(0,'-inserted bank account interface record.');
Line: 503

		    print_debug(0,'-inserting contact interface record.');
Line: 529

				cust_rec.insert_update_flag,
				l_cont_rec.email_address,
				-1,
				sysdate,
				-1,
				sysdate;
Line: 553

				cust_rec.insert_update_flag,
				l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_line_type,
				l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_number,
				l_cont_rec.phone_area_code,
				l_cont_rec.phone_extension,
				l_cont_rec.email_address,
				-1,
				sysdate,
				-1,
				sysdate;
Line: 564

			    print_debug(0,'-inserted contact-telephone interface record.');
Line: 578

		    print_debug(0,'-updated transfer flag .');
Line: 591

			print_debug(0,'Insert error: '||sqlerrm);
Line: 624

SELECT  bbr.bank_name, bbr.bank_branch_name
FROM    ce_bank_branches_v  bbr
WHERE 	bbr.branch_party_id = arp_global.CC_BANK_BRANCH_ID
';
Line: 634

SELECT
pbc.operator_id			operator_id,
pbc.billing_customer_id		billing_customer_id, '||
''''||g_osr_cust_prefix||''''||' ||to_char(pbc.bill_to_party_id)	orig_sys_cust_ref,'||
''''||g_osr_cust_prefix||''''||' ||to_char(pbc.bill_to_party_id)|| '||''''||g_osr_addr_prefix||''''||' ||to_char(pbc.bill_to_site_use_id)  orig_sys_addr_ref,
pbc.bill_to_party_id		bill_to_party_id,
pbc.bill_to_site_use_id		site_use_id,
pbc.bill_to_contact_party_id	contact_party_id,
pbc.creation_date		creation_date,
pbc.last_update_date		last_update_date,
substr(hp.party_name,1,50)	customer_name,
hp.party_number			party_number,
hloc.address1			address1,
hloc.address2			address2,
hloc.address3			address3,
hloc.address4			address4,
hloc.city			city,
hloc.county			county,
hloc.state			state,
hloc.country			country,
hloc.postal_plus4_code		postal_plus4_code,
pbc.bank_name			bank_name,
pbc.account_name		account_name,
pbc.account_number		account_number,
pbc.account_currency		account_currency,
pbc.account_description		account_description,
pbc.account_inactive_date 	account_exp_date,
pbc.payment_method_name		payment_method_code,
nvl(pbc.insert_update_flag,''I'')	insert_update_flag,
pbsp.org_id			org_id
FROM
pom_billing_customers'||g_oexdblink||' pbc,
hz_party_site_uses'||g_oexdblink||' hpsu,
hz_party_sites'||g_oexdblink||' hps,
hz_locations'||g_oexdblink||' hloc,
hz_parties'||g_oexdblink||' hp,
pom_billing_seat_parameters'||g_oexdblink||' pbsp
WHERE   hp.party_id 		= pbc.bill_to_party_id
AND     hps.party_id 		= hp.party_id
AND     hpsu.party_site_use_id 	= pbc.bill_to_site_use_id
AND	hpsu.site_use_type	= ''EXCHANGE_BILLING''
AND     hpsu.party_site_id 	= hps.party_site_id
AND     hps.location_id 	= hloc.location_id
AND	pbsp.operator_id	= pbc.operator_id
AND     pbc.request_id 		= :1
';
Line: 682

UPDATE  pom_billing_customers'||g_oexdblink||'
 SET 	ar_transfer_flag = ''E'',
	request_id = null,
	last_update_date = sysdate
WHERE   ar_transfer_flag = ''N''
AND	request_id = :1
';
Line: 691

UPDATE  pom_billing_customers'||g_oexdblink||'
 SET 	request_id = :1,
ar_transfer_flag = ''N'',
last_update_date = sysdate
WHERE   ( ((nvl(ar_transfer_flag,''~'') = ''N'') AND request_id is null)
OR	(nvl(ar_transfer_flag,''~'') = ''E'')
)
';
Line: 712

		print_debug(0,'Rows updated: '||to_char(sql%rowcount));
Line: 804

		Update records with error flag. Req-id stays so we can report
		on failed requests. Additionally we should insert failure
		codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
		error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
		*/
		print_debug(0,'ar_exchange_interface_pkg.customer_interface raised following exception: ');
Line: 851

	SELECT
		pba.bill_to_party_id			bill_to_party_id,
		pba.transaction_type 			trans_type,
		rtrim(pbat.billing_activity_type_name) 	activity_type_name,
		pbat.billing_activity_type_id		activity_type_id,
		pba.transaction_num 			trans_num,
		sum(pba.total_fee)			total_fee
	FROM
		pom_billing_activities'||g_oexdblink||' pba,
		pom_billing_activity_types_tl'||g_oexdblink||' pbat
	WHERE   pba.request_id = :1
	AND	pbat.billing_activity_type_id = pba.billing_activity_type_id
	AND	pbat.language_code = ''US''
	GROUP BY
		pba.bill_to_party_id,
		pba.transaction_type,
		rtrim(pbat.billing_activity_type_name),
		pbat.billing_activity_type_id,
		pba.transaction_num
	ORDER BY
		pba.bill_to_party_id,
		pba.transaction_type,
		pbat.billing_activity_type_id,
		pba.transaction_num
';
Line: 1012

	SELECT
		hp.party_name				customer_name,
		pbc.billing_customer_id			billing_customer_id,
		pbc.bill_to_site_use_id			bill_to_site_use_id,
		pbc.payment_method_name			payment_method_code,
		pbc.account_number			account_number,
		pbsp.org_id				org_id,
		pbsp.set_of_books_id			set_of_books_id,
		pbsp.orig_system_prefix			orig_system_prefix,
		pbsp.cust_trxtype_name			cust_trxtype_name,
		pbsp.payment_term_name			payment_term_name,
		pbsp.batch_source_name			batch_source_name,
		pbsp.interface_line_context		interface_line_context,'||
		''''||g_osr_cust_prefix||''''||'||to_char(pbc.bill_to_party_id)	orig_sys_cust_ref,'||
		''''||g_osr_cust_prefix||''''||'||to_char(pbc.bill_to_party_id)||'||''''||g_osr_addr_prefix||''''||' ||to_char(pbc.bill_to_site_use_id)  orig_sys_addr_ref
	FROM
		hz_parties'||g_oexdblink||' hp,
		pom_billing_customers'||g_oexdblink||' pbc,
		pom_billing_seat_parameters'||g_oexdblink||' pbsp
	WHERE
		hp.party_id 	     = pbc.bill_to_party_id
 	AND	pbsp.operator_id     = pbc.operator_id
	AND	pbc.bill_to_party_id = :1
';
Line: 1038

 SELECT ba.bank_account_id
 FROM 	ap_bank_account_uses_all bau,
	ap_bank_accounts_all ba,
	hz_cust_accounts_all hca
 WHERE 	bau.customer_id = hca.cust_account_id
 AND    hca.orig_system_reference = :1
 AND	ba.bank_account_num = :2
 AND	bau.external_bank_account_id = ba.bank_account_id
';
Line: 1049

INSERT INTO ra_interface_lines_all
(
org_id, batch_source_name, set_of_books_id, line_type,
currency_code, conversion_rate, conversion_type, description, memo_line_name,
amount, cust_trx_type_name, orig_system_bill_customer_ref, orig_system_bill_address_ref,
term_name, uom_code, trx_date, gl_date,
receipt_method_name, customer_bank_account_id ,
interface_line_context, interface_line_attribute1, interface_line_attribute2,
interface_line_attribute3 , interface_line_attribute4 , interface_line_attribute5 ,
reference_line_context, reference_line_attribute1, reference_line_attribute2,
reference_line_attribute3 , reference_line_attribute4 , reference_line_attribute5 ,
created_by, creation_date, last_updated_by, last_update_date,
quantity
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8, :9,
:10, :11, :12, :13,
:14, :15, :16, :17,
:18, :19,
:20, :21, :22,
:23, :24, :25,
:26, :27, :28,
:29, :30, :31,
:32, :33, :34, :35,
:36
FROM DUAL';
Line: 1078

 SELECT parameter_value
 FROM pom_operator_parameters'||g_oexdblink||'
 WHERE operator_party_id = '||g_oper_id||'
 AND parameter_name = ''oexOperDefaultCurrency''
';
Line: 1115

	UPDATE  pom_billing_activities'||g_oexdblink||'
	set 	request_id = :1
	WHERE	billing_activity_id in
		(select pba.billing_activity_id
		 from   pom_billing_activities'||g_oexdblink||' pba,
			pom_billing_customers'||g_oexdblink||' pbc,
			pom_billing_activity_types_tl'||g_oexdblink||' pbat
		where   pbc.bill_to_party_id 	= pba.bill_to_party_id
		AND	pbat.billing_activity_type_id = pba.billing_activity_type_id
		AND	pbat.language_code 	= ''US''
		and	pbc.operator_id 	= '||g_oper_id||'
		and	pbc.ar_transfer_flag is null
		and	pba.priced_flag is null
		and 	transaction_date < :2
		and 	( (nvl(pba.ar_transfer_flag,''~'') = ''N'' AND pba.request_id is null)
			OR
			(nvl(pba.ar_transfer_flag,''~'') = ''E'')
			)
		)
';
Line: 1427

				UPDATE  pom_billing_activities'||g_oexdblink ||'
				SET     ar_transfer_flag = NULL,
					last_billed_date  = :1,
					last_update_date = sysdate
				WHERE   bill_to_party_id = :2
				AND	request_id = :3
				';
Line: 1437

				print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
Line: 1502

				UPDATE  pom_billing_activities'||g_oexdblink||'
				SET 	ar_transfer_flag = NULL,
					last_billed_date  = :1,
					last_update_date = sysdate
				WHERE   bill_to_party_id = :2
				AND     request_id = :3
				';
Line: 1512

				print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
Line: 1515

		        -- Update last_billed_date in pom_billing_customers as the last step.
			IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
				begin
					l_temp_sql := '
					UPDATE pom_billing_customers'||g_oexdblink||'
					set last_billed_date  = :1
					where bill_to_party_id = :2
					';
Line: 1682

					    UPDATE  pom_billing_activities'||g_oexdblink||'
					    SET     request_id = :1
					    WHERE   bill_to_party_id = :2
			   		    AND	    transaction_type = :3
					    AND	    transaction_num = :4
					    AND	    request_id = :5
					    ';
Line: 1696

					print_debug(0,to_char(sql%rowcount)||' rows updated in pba for credit activities with [req_id = '||to_char(l_cm_request_id)||']');
Line: 1704

			   UPDATE  pom_billing_activities'||g_oexdblink||'
			   SET 	   ar_transfer_flag = ''P'',
				   last_billed_date = :1,
				   last_update_date = sysdate
			   WHERE   bill_to_party_id = :2
			   AND	   transaction_type = :3
			   AND	   transaction_num =  :4
			   AND	   request_id = :5
			   ';
Line: 1720

			   --print_debug(0,'Total fee = 0, updating trans['||ba_rec.trans_num||'] with status P, rows updated = '||to_char(sql%rowcount));
Line: 1800

			UPDATE  pom_billing_activities'||g_oexdblink||'
			SET     ar_transfer_flag = NULL,
				last_billed_date  = :1,
				last_update_date = sysdate
			WHERE   bill_to_party_id = :2
			AND	request_id = :3
			';
Line: 1813

			print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
Line: 1877

			UPDATE  pom_billing_activities'||g_oexdblink||'
			SET 	ar_transfer_flag = NULL,
				last_billed_date  = :1,
				last_update_date = sysdate
			WHERE   bill_to_party_id = :2
			AND     request_id = :3
			';
Line: 1887

			print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
Line: 1890

		-- Update last_billed_date in pom_billing_customers as the last step.
		IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
			begin
				l_temp_sql := '
				UPDATE pom_billing_customers'||g_oexdblink||'
				set last_billed_date  = :1
				where bill_to_party_id = :2
				';
Line: 1931

		Update records with error flag. Req-id stays so we can report
		on failed requests. Additionally we should insert failure
		codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
		error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
		*/
		print_debug(0,'invoice_interface raised following exception: ');
Line: 1946

		UPDATE  pom_billing_activities'||g_oexdblink||'
		SET 	ar_transfer_flag = ''E'',
			request_id = null,
			last_update_date = sysdate
		WHERE   request_id = :1
		';
Line: 1987

l_insert_pom_err varchar2(4000) ;
Line: 1991

l_insert_pom_err := '
INSERT INTO pom_billing_interface_errors'||g_oexdblink||'
(
billing_activity_id, billing_customer_id, customer_name,
error_code,
additional_message,
action_required,
invalid_value,
creation_date
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8
FROM DUAL
';
Line: 2019

		EXECUTE IMMEDIATE l_insert_pom_err
		USING
		p_billing_activity_id,
		p_billing_customer_id,
		p_customer_name,
		p_error_code,
		p_additional_message,
		p_action_required,
		nvl(p_invalid_value,'-'),
		sysdate;
Line: 2064

	EXECUTE IMMEDIATE 'select org_id from ar_system_parameters'
	INTO g_org_id;
Line: 2072

 select operator_id
 from pom_billing_seat_parameters'||g_oexdblink||'
 where org_id = :1
';
Line: 2096

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ERR'')
		       from dual';
Line: 2100

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ACT'')
		       from dual';
Line: 2104

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ERR'')
		       from dual';
Line: 2108

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ACT'')
		       from dual';
Line: 2112

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ERR'')
		       from dual';
Line: 2116

	l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ACT'')
		       from dual';