[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gta_trx_number
FROM ar_gta_trx_headers_all
WHERE ra_trx_id = p_ra_trx_id;
SELECT
transaction_id
FROM
ar_gta_transfer_temp
WHERE SUCCEEDED='Y';
SELECT
gta_trx_number
FROM
ar_gta_trx_headers_all
WHERE
ra_trx_id = p_ra_trx_id;
SELECT GT_CURRENCY_CODE
INTO l_currency
FROM ar_gta_system_parameters_all
WHERE org_id = p_org_id;
SELECT rule.rule_name
INTO l_transfer_rule
FROM ar_gta_rule_headers_all rule
WHERE rule.rule_header_id = p_transfer_id;
SELECT OU.NAME
INTO l_operation_unit
FROM HR_ALL_ORGANIZATION_UNITS O, HR_ALL_ORGANIZATION_UNITS_TL OU
WHERE O.ORGANIZATION_ID = OU.ORGANIZATION_ID
AND OU.LANGUAGE = USERENV('LANG')
AND O.ORGANIZATION_ID = p_org_id;
SELECT COUNT(*), SUM(nvl(amount, 0))
INTO l_succ_rows, l_succ_amount
FROM AR_gta_transfer_temp
WHERE SUCCEEDED = 'Y';
UPDATE
ar_gta_transfer_temp
SET
gta_invoice_num = l_gta_inv_num_all
WHERE
transaction_id = l_transaction_id;
SELECT COUNT(*), SUM(nvl(amount, 0))
INTO l_failed_rows, l_failed_amount
FROM AR_gta_transfer_temp
WHERE SUCCEEDED = 'N';
SELECT COUNT(*), SUM(nvl(amount, 0))
INTO l_warning_rows, l_warning_amount
FROM ar_gta_transfer_temp
WHERE SUCCEEDED = 'W';
SELECT COUNT(*)
INTO l_GTA_rows
FROM ar_gta_transfer_temp
WHERE SUCCEEDED = 'W'
OR SUCCEEDED = 'Y';
SELECT xmlelement("ReportFailed", 'N') INTO l_Reportfailed FROM dual;
SELECT xmlelement("FailedWithParameters", 'N')
INTO l_FailedWithParameters
FROM dual;
SELECT xmlelement("Parameters",
xmlforest(l_operation_unit AS "OperationUnit",
l_transfer_rule AS "TransferRule",
l_customer_num_from AS "CustomerNumberFrom",
l_customer_num_to AS "CustomerNumberTo",
l_customer_name_from AS "CustomerNameFrom",
l_customer_name_to AS "CustomerNameTo",
l_gl_period AS "GLPeriod",
l_gl_date_from AS "GLDateFrom",
l_gl_date_to AS "GLDateTo",
l_trx_batch_from AS "TransactionBatchFrom",
l_trx_batch_to AS "TransactionBatchTo",
l_trx_number_from AS "TransactionNumberFrom",
l_trx_number_to AS "TransactionNumberTo",
l_trx_date_from AS "TransactionDateFrom",
l_trx_date_to AS "TransactionDateTo",
l_doc_num_from AS "DocNumberFrom",
l_doc_num_to AS "DocNumberTo"))
INTO l_parameter
FROM dual;
SELECT xmlelement("Summary",
xmlforest(l_succ_rows AS "NumOfSucc",
l_failed_rows AS "NumOfFailed",
l_warning_rows AS "NumOfWarning",
l_GTA_rows AS "NumOfGTA",
l_succ_amount AS "AmountSucc",
l_failed_amount AS "AmountWarning",
l_warning_amount AS "AmountFail"))
INTO l_summary
FROM dual;
SELECT XMLElement("Invoices",
xmlagg(xmlelement("Invoice",
xmlforest(seq AS "sequence",
Transaction_Num AS
"TransactionNum",
Transaction_Type AS
"TransactionType",
Customer_Name AS
"CustomerName",
Amount AS "Amount",
FailedReason AS
"FailedReason"))))
INTO l_failed
FROM AR_gta_transfer_temp
WHERE SUCCEEDED = 'N';
SELECT XMLElement("Invoices",
xmlagg(xmlelement("Invoice",
xmlforest(seq AS "sequence",
Transaction_Num AS
"TransactionNum",
Transaction_Type AS
"TransactionType",
Customer_Name AS
"CustomerName",
Amount AS "Amount",
FailedReason AS
"WarningReason"))))
INTO l_warning
FROM AR_gta_transfer_temp
WHERE SUCCEEDED = 'W';
SELECT XMLElement("Invoices",
xmlagg(xmlelement("Invoice",
xmlforest(SEQ AS "sequence",
Transaction_Num AS
"TransactionNum",
Transaction_Type AS
"TransactionType",
Customer_Name AS
"CustomerName",
Amount AS "Amount"
--12/04/2006 Jogen Hu bug 5144561
/*gta_invoice_num AS "GTAInvoiceNum"*/,
get_gta_number(transaction_id) AS
"GTAInvoiceNum"
--12/04/2006 Jogen Hu bug 5144561
))))
INTO l_succeeded
FROM AR_gta_transfer_temp
WHERE SUCCEEDED = 'Y';
SELECT xmlelement("TransferReport",
xmlforest(l_reportFailed AS "ReportFailed",
l_FailedWithParameters AS
"FailedWithParameters",
AR_GTA_TRX_UTIL.To_Xsd_Date_String(SYSDATE) AS
"ReqDate"
--, to_char(SYSDATE, l_date_format) AS "ReqDate"
,
l_currency AS "CurrencyCode",
l_parameter AS "Parameters",
l_summary AS "Summary",
l_failed AS "FailedInvoices",
l_warning AS "WarningInvoices",
l_succeeded AS "SuccInvoices"))
INTO l_report_XML
FROM dual;
l_select_sql VARCHAR2(4000);
l_select_sql := 'SELECT h.customer_trx_id
FROM
ra_customer_trx_all h
, ra_cust_trx_types_all ctt
, ra_batches_all b
, Ra_Cust_Trx_Line_Gl_Dist_All gd
, Hz_Parties RAC_BILL_PARTY
, Hz_Cust_Accounts RAC_BILL
, GL_PERIODS GP
WHERE h.complete_flag = ''Y''
AND h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
AND ctt.TYPE IN (''INV'', ''CM'', ''DM'')
AND h.batch_id = b.batch_id(+)
AND GD.CUSTOMER_TRX_ID = h.CUSTOMER_TRX_ID
AND GD.ACCOUNT_CLASS = ''REC''
AND GD.LATEST_REC_FLAG = ''Y''
AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
AND h.Org_Id = gd.Org_Id
AND h.Org_Id = ctt.Org_Id
AND h.Org_Id =:p_org_id
AND GP.PERIOD_SET_NAME = (SELECT period_set_name
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = h.set_of_books_id)
AND gp.period_type = (SELECT accounted_period_type
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = h.set_of_books_id)
AND gp.adjustment_period_flag = ''N''
AND gp.start_date <= gd.GL_DATE
AND gp.end_date >= gd.gl_date ';
l_select_sql := l_select_sql || l_TRX_TYPE_condition;
l_select_sql := l_select_sql || l_flex_condition;
l_select_sql := l_select_sql || l_other_condition;
l_select_sql := l_select_sql || l_currency_condition;
l_select_sql := l_select_sql || l_invoice_type_condition;
x_query_sql := l_select_sql;
'l_select_sql:' || l_select_sql);
l_select_sql);
SELECT jgrha.invoice_type
INTO l_invoice_type_code
FROM ar_gta_rule_headers_all jgrha
WHERE jgrha.rule_header_id = p_transfer_id;
, 'no data found when select invoice_type.');
SELECT count(jgtm.transaction_type_id)
INTO l_transaction_type_cnt
FROM ar_gta_type_mappings jgtm, ar_gta_tax_limits_all jgtla
WHERE jgtla.limitation_id = jgtm.limitation_id
AND jgtla.invoice_type = l_invoice_type_code
AND jgtla.org_id = p_org_id;
'no data found when select invoice_type.');
(SELECT jgtm.transaction_type_id
FROM ar_gta_type_mappings jgtm
,ar_gta_tax_limits_all jgtla
WHERE jgtm.limitation_id = jgtla.limitation_id
AND jgtla.invoice_type = ''' ||
l_invoice_type_code || '''
AND jgtla.org_id = :p_org_id)';
SELECT l.cust_trx_type_id
FROM AR_GTA_RULE_TRX_TYPES_ALL l
WHERE l.rule_header_id = p_transfer_id
AND l.condition_rule = 'I';
SELECT l.cust_trx_type_id
FROM ar_gta_rule_trx_types_all l
WHERE l.rule_header_id = p_transfer_id
AND l.condition_rule = 'E';
SELECT l.context_code, l.attribute_column, l.attribute_value
FROM AR_GTA_RULE_DFFS_ALL l
WHERE l.org_id = P_ORG_ID
AND l.rule_header_id = p_transfer_id
AND l.condition_rule = 'I';
SELECT l.context_code, l.attribute_column, l.attribute_value
FROM ar_gta_rule_dffs_all l
WHERE l.Org_Id = P_ORG_ID
AND l.rule_header_id = p_transfer_id
AND l.condition_rule = 'E';
SELECT rule.currency_option, rule.specific_currency_code
INTO l_gta_currency_option, l_specific_currency_code
FROM ar_gta_rule_headers_all rule
WHERE rule.rule_header_id = p_transfer_id;
'no data found when select sales_list_flag.');
SELECT op.gt_currency_code
INTO l_gta_currency_code
FROM ar_gta_system_parameters_all op
WHERE op.org_id = p_ORG_ID;
'no data found when select sales_list_flag.');
SELECT l.customer_trx_line_id,
l.description,
l.inventory_item_id,
l.interface_line_context,
l.attribute_category,
l.uom_code,
l.revenue_amount,
l.unit_selling_price,
l.quantity_invoiced,
l.quantity_credited,
l.line_number, --12/06/2006 line number,Added by Shujuan bug 5230712
--Add by Yao Zhang begin for bug#8605196 to support Discount line
l.interface_line_attribute1, --order number
l.interface_line_attribute6, --line id
l.interface_line_attribute11 --price adjustment id
--Add by Yao Zhang end for bug#8605196 to support Discount line
FROM ra_customer_trx_lines_all l
WHERE l.line_type = 'LINE'
AND l.customer_trx_id = l_header_id;
SELECT opa.price_adjustment_id
FROM oe_price_adjustments opa
WHERE opa.line_id = l_line_id
AND opa.list_line_type_code = 'DIS';
SELECT vat_tax_type_code,
trx_line_split_flag,
gt_currency_code,
item_name_source_flag,
cross_reference_type,
master_item_default_flag,
latest_ref_default_flag,
ra_line_context_code,
ra_model_attribute_column,
ra_tax_attribute_column,
inv_item_context_code,
inv_model_attribute_column,
inv_tax_attribute_column,
gt_currency_code
INTO l_vat_tax_type,
l_trx_line_split_flag,
l_gt_currency_code,
l_item_name_source_flag,
l_cross_reference_type,
l_master_item_default_flag,
l_latest_ref_default_flag,
l_ra_line_context_code,
l_ra_model_attribute_column,
l_ra_tax_attribute_column,
l_inv_item_context_code,
l_inv_model_attribute_column,
l_inv_tax_attribute_column,
l_currency_code
FROM ar_gta_system_parameters_all
WHERE org_id = p_org_id;
SELECT sales_list_flag
INTO l_sales_list_flag
FROM ar_gta_rule_headers_all
WHERE ar_gta_rule_headers_all.rule_header_id = p_transfer_id;
'no data found when select sales_list_flag.');
SELECT COUNT(*)
INTO l_cust_trx_id_count
FROM ar_gta_trx_headers_all h
WHERE h.ra_trx_id = l_customer_trx_id;
SELECT COUNT(*)
INTO l_trx_id_cancel_count
FROM ar_gta_trx_headers_all h
WHERE h.ra_trx_id = l_customer_trx_id
AND h.latest_version_flag = 'Y'
AND h.status = 'CANCEL'
AND h.version > 1;
DELETE ar_gta_trx_headers_all h
WHERE h.ra_trx_id = l_customer_trx_id
AND h.latest_version_flag = 'Y'
AND h.status = 'CANCEL'
AND h.version > 1;
SELECT COUNT(*)
INTO l_trx_line_num
FROM ra_customer_trx_lines_all l
WHERE l.customer_trx_id = l_customer_trx_id;
SELECT h.trx_number,
gd.gl_date,
h.set_of_books_id,
h.bill_to_customer_id,
h.trx_date,
h.Invoice_Currency_Code,
h.exchange_rate_type,
h.exchange_rate,
h.legal_entity_id,
h.ct_reference,
ctt.TYPE,
gp.period_name
INTO l_trx_number,
l_gl_date,
l_set_of_books_id,
l_bill_to_customer_id,
l_trx_date,
l_invoice_Currency_code,
l_exchange_rate_type,
l_exchange_rate,
l_legal_entity_id,
l_ct_reference,
l_ctt_class,
l_period_name
FROM ra_customer_trx_all h,
ra_cust_trx_types_all ctt,
ra_batches_all b,
Ra_Cust_Trx_Line_Gl_Dist_All gd,
Hz_Parties RAC_BILL_PARTY,
Hz_Cust_Accounts RAC_BILL,
GL_PERIODS GP -- period
WHERE h.complete_flag = 'Y'
AND h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
AND ctt.TYPE IN ('INV', 'CM', 'DM')
AND h.batch_id = b.batch_id(+)
AND GD.CUSTOMER_TRX_ID = h.CUSTOMER_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
AND h.Org_Id = gd.Org_Id
AND h.Org_Id = ctt.Org_Id
AND h.Org_Id = p_org_id
AND GP.PERIOD_SET_NAME =
(SELECT period_set_name
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = h.set_of_books_id)
AND gp.period_type =
(SELECT accounted_period_type
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = h.set_of_books_id)
AND gp.adjustment_period_flag = 'N'
AND gp.start_date <= gd.GL_DATE
AND gp.end_date >= gd.gl_date
AND h.customer_trx_id = l_customer_trx_id;
'no date found when select header info');
SELECT
--Modified by Yao to support customer address in Chinese
DECODE(RAA_BILL.CUST_ACCT_SITE_ID,
NULL,
NULL,
decode(RAA_BILL_LOC.Address_Lines_Phonetic,
null,
ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_BILL_LOC.ADDRESS_STYLE,
RAA_BILL_LOC.ADDRESS1,
RAA_BILL_LOC.ADDRESS2,
RAA_BILL_LOC.ADDRESS3,
RAA_BILL_LOC.ADDRESS4,
RAA_BILL_LOC.CITY,
RAA_BILL_LOC.COUNTY,
RAA_BILL_LOC.STATE,
RAA_BILL_LOC.PROVINCE,
RAA_BILL_LOC.POSTAL_CODE,
FT_BILL.TERRITORY_SHORT_NAME),
RAA_BILL_LOC.Address_Lines_Phonetic))
INTO l_raa_bill_to_concat_address
FROM HZ_CUST_SITE_USES_ALL SU_BILL,
Hz_Cust_Acct_Sites_All RAA_BILL,
HZ_PARTY_SITES RAA_BILL_PS,
Hz_Locations RAA_BILL_LOC,
FND_TERRITORIES_VL FT_BILL,
ra_customer_trx_all h
WHERE h.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_PS.LOCATION_ID = RAA_BILL_LOC.LOCATION_ID
AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
AND h.customer_trx_id = l_customer_trx_id;
'no date found when select l_raa_bill_to_concat_address');
SELECT p.phone_number
INTO l_phone_number
FROM Hz_Contact_Points p
--Yao delete for bug#8769687 begin
--,Hz_Cust_Accounts RAC_BILL
--,Hz_Parties RAC_BILL_PARTY
--Yao delete end for bug#8769687
,
ra_customer_trx_all h
--Yao add for bug#8769687
,
hz_party_sites hps,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
--Yao add end
WHERE -- h.bill_to_customer_id can find by customer trx id
--h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
-- AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
--AND RAC_BILL_PARTY.Party_Id = p.owner_table_id(+)
--Yao Zhang add for bug#8769687 begin
h.bill_to_site_use_id = hcsua.SITE_USE_ID
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hps.party_site_id = hcasa.party_site_id
AND p.owner_table_id(+) = hcasa.party_site_id
--Yao add or bug#8769687 end
AND p.owner_table_name(+) = 'HZ_PARTY_SITES'
AND p.CONTACT_POINT_TYPE(+) = 'PHONE'
AND p.primary_flag(+) = 'Y'
AND h.customer_trx_id = l_customer_trx_id;
'no date found when select phone number');
SELECT
--Modified by Yao begin for bug#8605196 to support customer name in Chinese
--RAC_BILL_PARTY.PARTY_NAME,
decode(RAC_BILL_PARTY.Known_As,
null,
RAC_BILL_PARTY.PARTY_NAME,
RAC_BILL_PARTY.Known_As),
--Modified by Yao end for bug#8605196 to support customer name in Chinese
RAC_BILL.ACCOUNT_NUMBER,
RAC_BILL_PARTY.JGZZ_FISCAL_CODE
INTO l_rac_bill_to_customer_name,
l_rac_bill_to_customer_num,
l_bill_to_taxpayer_id
FROM ra_customer_trx_all h,
Hz_Cust_Accounts rac_bill,
Hz_Parties rac_bill_party
WHERE h.customer_trx_id = l_customer_trx_id
AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id;
'no data found when select rac information');
SELECT count(*)
INTO l_ar_inv_cnt
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL PS_INV
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND app.ORG_ID = p_org_id
AND app.CUSTOMER_TRX_ID = l_customer_trx_id
AND app.display = 'Y';
FOR l_ar_cur IN (SELECT PS_INV.CUSTOMER_TRX_ID trx_id
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL PS_INV
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND app.ORG_ID = p_org_id
AND app.CUSTOMER_TRX_ID = l_customer_trx_id
AND app.display = 'Y')
LOOP
l_origin_trx_id := l_ar_cur.trx_id; --taking the AR trx id for which credit memo assigned for getting Bank info
SELECT
count(*)
INTO
l_gta_invoice_count
FROM
AR_Gta_Trx_Headers_All
WHERE ra_trx_id=l_ar_cur.trx_id
AND SOURCE = 'AR';
SELECT
count(*)
INTO
l_gt_invoice_count
FROM
AR_Gta_Trx_Headers_All
WHERE ra_trx_id=l_ar_cur.trx_id
AND SOURCE = 'GT';
SELECT ar_gta_trx_headers_all_s.NEXTVAL
INTO l_trx_header.gta_trx_header_id
FROM dual;
SELECT previous_customer_trx_id
INTO l_origin_trx_id
FROM ra_customer_trx_all
WHERE customer_trx_id = l_customer_trx_id;
l_trx_header.program_update_date := SYSDATE;
l_trx_header.last_update_date := SYSDATE;
l_trx_header.last_updated_by := fnd_global.USER_ID();
l_trx_header.last_update_login := fnd_global.LOGIN_ID();
SELECT parameter_value
INTO l_discount_on_invoice
FROM oe_sys_parameters_all
WHERE org_id = p_org_id
AND parameter_code = 'OE_DISCOUNT_DETAILS_ON_INVOICE';
SELECT rctl.revenue_amount + nvl(l_discount_amount, 0),
tax.taxable_amt_tax_curr + --yao add for bug 9132371
nvl(l_discount_amount_func_curr, 0),
rctl.customer_trx_line_id
INTO l_discount_amount,
l_discount_amount_func_curr,
l_discount_cust_trx_line_id
FROM ra_customer_trx_lines_all rctl, zx_lines tax
WHERE rctl.customer_trx_id = l_customer_trx_id
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 =
l_discount_adjustment_id
AND rctl.customer_trx_line_id = tax.trx_line_id
--yao add begin for bug 9132371
AND tax.entity_code = 'TRANSACTIONS'
AND tax.application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = l_currency_code
AND tax.tax_type_code = l_vat_tax_type
AND tax.trx_id = l_customer_trx_id;
SELECT tax.tax_amt_tax_curr +
nvl(l_discount_tax_amount, 0)
INTO l_discount_tax_amount
FROM zx_lines tax
WHERE tax.trx_line_id = l_discount_cust_trx_line_id
AND tax.entity_code = 'TRANSACTIONS'
AND tax.application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = l_currency_code
AND tax.tax_type_code = l_vat_tax_type
AND tax.trx_id = l_customer_trx_id;
SELECT tax.tax_amt_tax_curr + nvl(l_tax_amount, 0)
INTO l_tax_amount
FROM zx_lines tax
WHERE tax.trx_line_id = l_customer_trx_line_id
AND tax.entity_code = 'TRANSACTIONS'
AND application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = l_currency_code
AND tax.tax_type_code = l_vat_tax_type
AND tax.trx_id = l_customer_trx_id;
IF --ABS(l_discount_rate-l_discount_tax_rate)>0.001 delete for bug#8920239
ABS(l_discount_tax_amount -
l_discount_tax_rate * l_tax_amount) > 0.01 THEN
fnd_message.SET_NAME('AR', 'AR_GTA_DIF_DIS_RATE');
SELECT opa.list_line_type_code
INTO l_adjustment_type
FROM oe_price_adjustments opa
WHERE opa.price_adjustment_id = l_price_adjustment_id;
SELECT parameter_value
INTO l_master_org
FROM oe_sys_parameters_all
WHERE org_id = p_org_id
AND parameter_code = 'MASTER_ORGANIZATION_ID';
SELECT jgrha.invoice_type
INTO l_invoice_type_code
FROM ar_gta_rule_headers_all jgrha
WHERE jgrha.rule_header_id = p_transfer_id;
SELECT previous_customer_trx_id
INTO l_pre_cus_trxid
FROM ra_customer_trx_all
WHERE customer_trx_id = l_customer_trx_id;
SELECT COUNT(*)
INTO l_ar_invoice_count
FROM ar_gta_trx_headers_all
WHERE ra_trx_id = l_pre_cus_trxid
AND SOURCE = 'AR';
SELECT count(*)
INTO l_gt_invoice_count
FROM AR_Gta_Trx_Headers_All
WHERE ra_trx_id = l_pre_cus_trxid
AND source = 'GT'; --Yao Zhang Modified fix bug 7670543
SELECT gt_invoice_number, gt_invoice_class
INTO l_gt_invoice_number, l_gt_invoice_class
FROM AR_Gta_Trx_Headers_All
WHERE ra_trx_id = l_pre_cus_trxid
AND source = 'GT';
DELETE FROM ar_gta_transfer_temp temp
WHERE temp.transaction_id = l_customer_trx_id
AND temp.succeeded = 'W'
--and temp.tax_reg_num=l_tp_registration_number;--Modified by Yao Zhang for bug 7684662
INSERT INTO ar_gta_transfer_temp t
(t.seq,
t.transaction_id,
t.succeeded,
t.transaction_num,
t.transaction_type,
t.customer_name,
t.amount,
t.failedreason,
t.gta_invoice_num,
t.tax_reg_num)
SELECT ar_gta_transfer_temp_s.NEXTVAL,
l_customer_trx_id,
'W',
l_trx_number,
l_ctt_class,
l_rac_bill_to_customer_name,
NULL,
l_error_string,
NULL,
l_tp_registration_number --added by Yao Zhang for bug 7644235
-- to distinguish different tax reg number on trx lines.
FROM dual;
'no data found when select cust address for non-common VAT invoice');
l_error_string := 'no data found when select cust address for non-common VAT invoice';
'no data found when select customer phone number for non-common VAT invoice');
l_error_string := 'no data found when select customer phone number for non-common VAT invoice';
SELECT name
INTO l_trx_typ
FROM ra_cust_trx_types_all rctt,
ra_customer_trx_all rct
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id(+)
AND rct.org_id = rctt.org_id(+)
AND rct.customer_trx_id = l_customer_trx_id
AND rct.org_id = p_org_id;
SELECT limits.max_amount, limits.max_num_of_line
INTO l_max_amount, l_max_num_of_line
FROM ar_gta_tax_limits_all limits
WHERE limits.fp_tax_registration_number =
l_fp_registration_number
AND limits.invoice_type = l_invoice_type
AND limits.org_id = p_org_id;
SELECT unit_price_split_flag
INTO l_unit_price_split_flag
FROM AR_GTA_SYSTEM_PARAMETERS_all
WHERE org_id = p_org_id;
SELECT parameter_value
INTO l_master_org
FROM oe_sys_parameters_all
WHERE org_id = p_org_id
AND parameter_code = 'MASTER_ORGANIZATION_ID';
SELECT COUNT(*)
INTO l_cross_rows
FROM MTL_CROSS_REFERENCES
WHERE (organization_id IS NULL OR
organization_id = l_master_org) --yao zhang modified for bug 7721035
AND inventory_item_id = l_inventory_item_id
AND cross_reference_type = l_cross_reference_type;
SELECT MAX(cross_reference)
INTO l_cross_reference
FROM MTL_CROSS_REFERENCES
WHERE (organization_id IS NULL OR
organization_id = l_master_org) --yao zhang modified for bug 7721035
AND inventory_item_id = l_inventory_item_id
AND cross_reference_type = l_cross_reference_type
AND last_update_date =
(SELECT MAX(last_update_date)
FROM MTL_CROSS_REFERENCES
WHERE (organization_id IS NULL OR
organization_id = l_master_org) --yao zhang modified for bug 7721035
AND inventory_item_id = l_inventory_item_id
AND cross_reference_type =
l_cross_reference_type);
SELECT DESCRIPTION, attribute_category
INTO l_inventory_item_name,
l_inventory_attribute_category
FROM mtl_system_items_b
WHERE organization_id = l_master_org --yao zhang modified for bug 7721035
AND inventory_item_id = l_inventory_item_id;
l_trx_line.program_update_date := SYSDATE;
l_trx_line.last_update_date := SYSDATE;
l_trx_line.last_updated_by := fnd_global.USER_ID();
l_trx_line.last_update_login := fnd_global.CONC_LOGIN_ID();
DELETE ar_gta_transfer_temp temp
WHERE temp.transaction_id = l_customer_trx_id
AND temp.succeeded = 'W';
INSERT INTO ar_gta_transfer_temp t
(t.seq,
t.transaction_id,
t.succeeded,
t.transaction_num,
t.transaction_type,
t.customer_name,
t.amount,
t.failedreason,
t.gta_invoice_num)
SELECT ar_gta_transfer_temp_s.NEXTVAL,
l_customer_trx_id,
'N',
l_trx_number,
l_ctt_class,
l_rac_bill_to_customer_name,
NULL,
l_error_string,
NULL
FROM dual;
SELECT attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30
INTO l_inventory_attribute1,
l_inventory_attribute2,
l_inventory_attribute3,
l_inventory_attribute4,
l_inventory_attribute5,
l_inventory_attribute6,
l_inventory_attribute7,
l_inventory_attribute8,
l_inventory_attribute9,
l_inventory_attribute10,
l_inventory_attribute11,
l_inventory_attribute12,
l_inventory_attribute13,
l_inventory_attribute14,
l_inventory_attribute15,
l_inventory_attribute16,
l_inventory_attribute17,
l_inventory_attribute18,
l_inventory_attribute19,
l_inventory_attribute20,
l_inventory_attribute21,
l_inventory_attribute22,
l_inventory_attribute23,
l_inventory_attribute24,
l_inventory_attribute25,
l_inventory_attribute26,
l_inventory_attribute27,
l_inventory_attribute28,
l_inventory_attribute29,
l_inventory_attribute30
FROM mtl_system_items_b
WHERE organization_id = p_item_master_org_id --yao zhang modified for bug 7829039
AND inventory_item_id = p_inventory_item_id;
SELECT interface_line_attribute1,
interface_line_attribute2,
interface_line_attribute3,
interface_line_attribute4,
interface_line_attribute5,
interface_line_attribute6,
interface_line_attribute7,
interface_line_attribute8,
interface_line_attribute9,
interface_line_attribute10,
interface_line_attribute11,
interface_line_attribute12,
interface_line_attribute13,
interface_line_attribute14,
interface_line_attribute15
INTO l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15*/
SELECT attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20
INTO l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_global_attribute1,
l_global_attribute2,
l_global_attribute3,
l_global_attribute4,
l_global_attribute5,
l_global_attribute6,
l_global_attribute7,
l_global_attribute8,
l_global_attribute9,
l_global_attribute10,
l_global_attribute11,
l_global_attribute12,
l_global_attribute13,
l_global_attribute14,
l_global_attribute15,
l_global_attribute16,
l_global_attribute17,
l_global_attribute18,
l_global_attribute19,
l_global_attribute20
FROM ra_customer_trx_lines_all l
WHERE l.customer_trx_line_id = p_ra_line_id;
SELECT msv.concatenated_segments
INTO l_inventory_item_code
FROM MTL_SYSTEM_ITEMS_B_KFV msv
WHERE msv.inventory_item_id = p_inventory_item_id --yao zhang changed fix bug 7829039
AND msv.organization_id = p_item_master_org_id;
SELECT uom.unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure_tl uom
WHERE uom.uom_code = p_uom_code
AND uom.LANGUAGE = userenv('LANG');
'no data found when select receiving_routing_id by line_location_id' ||
SQLCODE || SQLERRM);