[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jgct.seq FROM AR_gta_consol_temp jgct WHERE status = l_status;
SELECT otl.NAME
INTO l_org_name
FROM hr_all_organization_units o, hr_all_organization_units_tl otl
WHERE o.organization_id = otl.organization_id
AND otl.LANGUAGE = userenv('LANG')
AND o.organization_id = l_org_id;
SELECT COUNT(*)
INTO l_succ_unm
FROM AR_gta_consol_temp
WHERE status= 'S';
SELECT COUNT(*)
INTO l_warn_unm
FROM AR_gta_consol_temp
WHERE status= 'W';
SELECT COUNT(*)
INTO l_error_unm
FROM AR_gta_consol_temp
WHERE status= 'E';
SELECT xmlelement("ReportFailed", 'N') INTO l_Reportfailed FROM dual;
SELECT xmlelement("FailedWithParameters", 'N')
INTO l_failedwithparameters
FROM dual;
SELECT xmlelement("Parameters",
xmlforest(l_sameprisamedis AS "SamePriSameDis",
l_samepridiffdis AS "SamePriDiffDis",
l_diffpri AS "DiffPri",
l_saleslistflag AS "SalesList",
l_consolidation_id AS "ConsolidationId",
l_org_name AS "OrgName"
))
INTO l_parameter
FROM dual;
SELECT xmlelement("Summary",
xmlforest(l_succ_unm AS "NumOfSucc",
l_warn_unm AS "NumOfWarning",
l_error_unm AS "NumOfFailed"))
INTO l_summary
FROM dual;
SELECT xmlagg(xmlelement("ConsolidatedInv",
xmlforest(jgcit.consolidated_inv_number AS
"Consolidated",
jgcit.gl_period AS "GLPeriod",
jgcit.ra_trx_num AS "RATrxNum",
jgcit.ra_trx_type AS "RATrxType",
--modified by Jixun for bug#16274922 begin
--jgcit.amount AS "Amount"
ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
--modified by Jixun for bug#16274922 end
)))
INTO l_success_invs
FROM AR_gta_consol_invs_temp jgcit
WHERE jgcit.seq = l_consol_seq;
SELECT xmlelement("ConsolidationInv",
xmlforest(jgct.seq AS "SEQ",
jgct.consolidation_inv_number AS
"ConsolidationInvNum",
jgct.customer_name AS "CustomerName",
jgct.tp_tax_reg_num AS "TPTaxRegNum",
jgct.customer_address_phone AS
"CustomerAddrPhone",
jgct.bank_account_name AS "BankName",
jgct.bank_account_num AS
"BankAccountNumber",
lk.meaning AS "InvoiceType",
--modified by Jixun for bug#16274922 begin
--jgct.amount AS "Amount",
ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
--modified by Jixun for bug#16274922 end
jgct.failed_reason AS "FailedReason",
l_success_invs AS "ConsolidatedInvs"))
INTO l_success
FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
WHERE jgct.seq = l_consol_seq
AND jgct.invoice_type = lk.lookup_code
AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
SELECT Xmlconcat(l_success_all, l_success)
INTO l_success_all
FROM dual;
SELECT xmlagg(xmlelement("ConsolidatedInv",
xmlforest(jgcit.consolidated_inv_number AS
"Consolidated",
jgcit.gl_period AS "GLPeriod",
jgcit.ra_trx_num AS "RATrxNum",
jgcit.ra_trx_type AS "RATrxType",
--modified by Jixun for bug#16274922 begin
--jgcit.amount AS "Amount"
ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
--modified by Jixun for bug#16274922 end
)))
INTO l_warning_invs
FROM AR_gta_consol_invs_temp jgcit
WHERE jgcit.seq = l_consol_seq;
SELECT xmlagg(xmlelement("ConsolidationInv",
xmlforest(jgct.SEQ AS "SEQ",
jgct.consolidation_inv_number AS
"ConsolidationInvNum",
jgct.customer_name AS "CustomerName",
jgct.tp_tax_reg_num AS "TPTaxRegNum",
jgct.customer_address_phone AS
"CustomerAddrPhone",
jgct.bank_account_name AS "BankName",
jgct.bank_account_num AS
"BankAccountNumber",
lk.meaning AS "InvoiceType",
--modified by Jixun for bug#16274922 begin
--jgct.amount AS "Amount",
ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
--modified by Jixun for bug#16274922 end
jgct.failed_reason AS "FailedReason",
l_warning_invs AS "ConsolidatedInvs")))
INTO l_warning
FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
WHERE jgct.seq = l_consol_seq
AND jgct.invoice_type = lk.lookup_code
AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
SELECT Xmlconcat(l_warning_all, l_warning)
INTO l_warning_all
FROM dual;
SELECT xmlagg(xmlelement("ConsolidatedInv",
xmlforest(jgcit.consolidated_inv_number AS
"Consolidated",
jgcit.gl_period AS "GLPeriod",
jgcit.ra_trx_num AS "RATrxNum",
jgcit.ra_trx_type AS "RATrxType",
--modified by Jixun for bug#16274922 begin
--jgcit.amount AS "Amount"
ar_gta_trx_util.format_monetary_amount(l_org_id, jgcit.amount) AS "Amount"
--modified by Jixun for bug#16274922 end
)))
INTO l_failed_invs
FROM AR_gta_consol_invs_temp jgcit
WHERE jgcit.seq = l_consol_seq;
SELECT xmlagg(xmlelement("ConsolidationInv",
xmlforest(jgct.SEQ AS "SEQ",
jgct.consolidation_inv_number AS
"ConsolidationInvNum",
jgct.customer_name AS
"CustomerName",
jgct.tp_tax_reg_num AS
"TPTaxRegNum",
jgct.customer_address_phone AS
"CustomerAddrPhone",
jgct.bank_account_name AS
"BankName",
jgct.bank_account_num AS
"BankAccountNumber",
lk.meaning AS "InvoiceType",
--modified by Jixun for bug#16274922 begin
--jgct.amount AS "Amount",
ar_gta_trx_util.format_monetary_amount(l_org_id, jgct.amount) AS "Amount",
--modified by Jixun for bug#16274922 end
jgct.failed_reason AS
"FailedReason",
l_failed_invs AS
"ConsolidatedInvs")))
INTO l_failed
FROM AR_gta_consol_temp jgct, fnd_lookup_values_vl lk
WHERE jgct.seq = l_consol_seq
AND jgct.invoice_type = lk.lookup_code
AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
SELECT Xmlconcat(l_failed_all, l_failed) INTO l_failed_all FROM dual;
SELECT xmlelement("ConsolidationReport",
xmlforest(l_reportfailed AS "ReportFailed",
l_failedwithparameters AS
"FailedWithParameters",
ar_gta_trx_util.to_xsd_date_string(SYSDATE) AS
"ReqDate",
l_parameter AS "Parameters",
l_summary AS "Summary",
l_success_all AS "SuccessInvs",
l_warning_all AS "WarningInvs",
l_failed_all AS "FailedInvs"))
INTO l_report_xml
FROM dual;
Invoices with different UOMs in selected GTA Invoices.
-------------------------------------------------------*/
l_diff_uoms_flag NUMBER;
SELECT jgth.fp_tax_registration_number,
jgth.bill_to_customer_name,
jgth.tp_tax_registration_number,
jgth.customer_address_phone,
jgth.bank_account_name,
jgth.bank_account_number,
jgth.invoice_type,
jgtl.tax_rate --Yao add for bug9655856
FROM ar_gta_trx_headers_all jgth, ar_gta_trx_lines_all jgtl
WHERE jgth.consolidation_id = p_consolidation_id
--Yao add for bug 9655856
AND jgth.gta_trx_header_id = jgtl.gta_trx_header_id
AND jgth.org_id = jgtl.org_id
--Yao add end for bug 9655856
GROUP BY jgth.fp_tax_registration_number,
jgth.bill_to_customer_name,
jgth.tp_tax_registration_number,
jgth.customer_address_phone,
jgth.bank_account_name,
jgth.bank_account_number,
jgth.invoice_type,
jgtl.tax_rate; --Yao add for bug9655856
SELECT jgth.gta_trx_header_id
FROM ar_gta_trx_headers_all jgth
WHERE jgth.consolidation_id = p_consolidation_id
AND jgth.fp_tax_registration_number=p_fp_tax_registration_number
AND jgth.bill_to_customer_name = p_bill_to_customer_name
AND (jgth.tp_tax_registration_number = p_tp_tax_registration_number OR
decode(p_tp_tax_registration_number,
NULL,
jgth.tp_tax_registration_number,
p_tp_tax_registration_number) IS NULL)
AND (jgth.customer_address_phone = p_customer_address_phone OR
decode(p_customer_address_phone,
NULL,
jgth.customer_address_phone,
p_customer_address_phone) IS NULL)
AND (jgth.bank_account_name = p_bank_account_name OR
decode(p_bank_account_name,
NULL,
jgth.bank_account_name,
p_bank_account_name) IS NULL)
AND (jgth.bank_account_number = p_bank_account_number OR
decode(p_bank_account_number,
NULL,
jgth.bank_account_number,
p_bank_account_number) IS NULL)
AND jgth.invoice_type = p_invoice_type
--Yao add for bug 9655856
AND jgth.gta_trx_header_id = (SELECT jgtl.gta_trx_header_id
FROM ar_gta_trx_lines_all jgtl
WHERE jgtl.tax_rate=p_tax_rate
AND jgth.gta_trx_header_id = jgtl.gta_trx_header_id
AND jgth.org_id = jgtl.org_id
GROUP BY jgtl.gta_trx_header_id)
--Yao add end for bug9655856
ORDER BY jgth.gta_trx_number;
SELECT jgtla.max_amount, jgtla.max_num_of_line
INTO l_max_amount, l_max_line
FROM ar_gta_tax_limits_all jgtla
WHERE jgtla.fp_tax_registration_number =
l_fp_tax_registration_number
AND jgtla.invoice_type = l_invoice_type
AND jgtla.org_id = l_org_id;--Yao Zhang add for bug#8770356
l_csldtion_inv_line.last_update_date :=SYSDATE;
l_csldtion_inv_line.last_updated_by := fnd_global.USER_ID();
l_csldtion_inv_line.last_update_login := fnd_global.LOGIN_ID();
l_csldtion_inv_line.PROGRAM_UPDATE_DATE :=SYSDATE;
Invoices with same UOM, but if there are different UOMs in selected positive
invoices, it will consolidate fail.
----------------------------------------------------------------------------*/
l_csldtion_inv_lines_index := l_csldtion_inv.trx_lines.first;
l_csldtion_inv.trx_lines.delete(l_csldtion_inv_lines_index1);
l_csldtion_inv.trx_lines.DELETE(l_csldtion_inv_lines_index);
SELECT ar_gta_trx_headers_all_s.NEXTVAL
INTO l_csldtion_inv.trx_header.gta_trx_header_id
FROM dual;
l_csldtion_inv.trx_header.program_update_date := SYSDATE;
l_csldtion_inv.trx_header.last_update_date := SYSDATE;
l_csldtion_inv.trx_header.last_updated_by := fnd_global.USER_ID();----Qiong fix bug 10638369
l_csldtion_inv.trx_header.last_update_login := fnd_global.LOGIN_ID();
SELECT MAX(group_number)+1
INTO l_csldtion_inv.trx_header.group_number
FROM ar_gta_trx_headers_all jgth
WHERE jgth.gta_trx_number LIKE l_csldted_inv.trx_header.ra_trx_id||'-%';
SELECT ar_gta_trx_lines_all_s.NEXTVAL
INTO l_csldtion_inv.trx_lines(l_csldtion_inv_lines_index).gta_trx_line_id
FROM dual;
UPDATE ar_gta_trx_headers_all
SET status = 'CONSOLIDATED',
consolidation_flag = '1',
consolidation_trx_num = l_csldtion_inv.trx_header.gta_trx_number
WHERE gta_trx_header_id = l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
SELECT AR_gta_consol_temp_s.NEXTVAL
INTO l_gta_consol_temp_seq
FROM dual;
INSERT INTO AR_gta_consol_temp
(seq
,status
,consolidation_inv_number
,customer_name
,tp_tax_reg_num
,customer_address_phone
,bank_account_name
,bank_account_num
,invoice_type
,amount
,failed_reason)
SELECT
l_gta_consol_temp_seq
,l_result_flag
,l_csldtion_inv.trx_header.gta_trx_number
,l_bill_to_customer_name
,l_tp_tax_registration_number
,l_customer_address_phone
,l_bank_account_name
,l_bank_account_number
,l_invoice_type
,l_sum_amount
,l_error_string
FROM dual;
UPDATE ar_gta_trx_headers_all
SET consolidation_id=NULL
WHERE GTA_TRX_HEADER_ID=l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
SELECT jgthv.ra_trx_type
,jgthv.amount
INTO l_ra_trx_type
,l_amount
FROM AR_GTA_TRX_HEADERS_V jgthv
WHERE jgthv.GTA_TRX_HEADER_ID=l_csldted_invs(l_csldted_invs_index).trx_header.gta_trx_header_id;
INSERT INTO AR_gta_consol_invs_temp
(seq
,consolidated_inv_number
,gl_period
,ra_trx_num
,ra_trx_type
,amount)
SELECT
l_gta_consol_temp_seq
,l_csldted_invs(l_csldted_invs_index).trx_header.GTA_TRX_NUMBER
,l_csldted_invs(l_csldted_invs_index).trx_header.ra_gl_period
,l_csldted_invs(l_csldted_invs_index).trx_header.RA_TRX_NUMBER
,l_ra_trx_type
,l_amount
FROM dual;
UPDATE ar_gta_trx_headers_all
SET consolidation_id = NULL
,consolidation_trx_num=NULL
,consolidation_flag=NULL
,status='DRAFT'
WHERE consolidation_id = l_consolidation_id;
fnd_file.PUT_LINE(fnd_file.LOG,'Update consolidation id'||l_consolidation_id);