[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
COUNT(*)
INTO
l_count
FROM
AR_Gta_Trx_Headers_All Gt
WHERE Gt.Ra_Trx_Id = p_ar_trx_header_id
AND Source='GT'
AND Status='COMPLETED'
AND (Gt_Invoice_Number NOT BETWEEN p_Gt_Inv_Num_From
AND p_Gt_Inv_Num_To
OR Gt_Invoice_Date NOT BETWEEN p_Gt_Inv_Date_From
AND p_Gt_Inv_Date_To
OR Fp_Tax_Registration_Number <> NVL(p_fp_tax_reg_num
,Fp_Tax_Registration_Number)
);
SELECT Xmlforest(
ract.Trx_Number AS "ARInvoiceNo"
,Get_Gt_Count
( p_ar_trx_header_id
, p_fp_tax_reg_num
, P_Gt_Inv_Date_From
, P_Gt_Inv_Date_To
, P_Gt_Inv_Num_From
, P_Gt_Inv_Num_To
) AS "Split"
--yao zhang modified for bug8766075
-- ,bat.name AS "ARSource"
,bas.name AS "ARSource"
-- modified by Allen Yang 24-Sep-2009 for bug 8920326
-----------------------------------------------------------------
,al.meaning AS "ARClass"
--,CTT.TYPE AS "ARClass"
------------------------------------------------------------------
,ar_gta_trx_util.To_Xsd_Date_String(ract.Trx_Date) AS "ARDate"
,RAC_BILL_PARTY.PARTY_NAME AS "ARCustomer"
,AR_Gta_Trx_Util.Get_Arinvoice_Amount
( P_Org_Id
, ract.Customer_Trx_Id
) AS "ARAmount"
,AR_Gta_Trx_Util.Get_Arinvoice_Tax_Amount
( P_Org_Id
, ract.Customer_Trx_Id
) AS "ARTaxAmount"
,AR_Gta_Trx_Util.Get_Arinvoice_Amount
( P_Org_Id
, ract.Customer_Trx_Id
)
+AR_Gta_Trx_Util.Get_Arinvoice_Tax_Amount
( P_Org_Id
, ract.Customer_Trx_Id
) AS "ARTotalAmount"
)
INTO
L_Ret_Xmlelement
FROM
Ra_Customer_Trx_all ract
--yao zhang modified for bug8766075
-- , ra_batches_all bat
,RA_BATCH_SOURCES_ALL bas
, Ra_Cust_Trx_Types_all ctt
, Hz_Cust_Accounts RAC_BILL
, Hz_Parties RAC_BILL_PARTY
-- added by Allen Yang 24-Sep-2009 for bug 8920326
----------------------------------------------------
, AR_LOOKUPS al
----------------------------------------------------
WHERE Customer_Trx_Id = P_Ar_Trx_Header_Id
AND ract.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ract.org_id = ctt.org_id
--yao zhang modified for bug8766075
--AND ract.batch_id = bat.batch_id(+)
AND ract.batch_source_id = bas.BATCH_SOURCE_ID(+)
AND ract.org_id =bas.org_id
AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id
-- added by Allen Yang 24-Sep-2009 for bug 8920326
----------------------------------------------------------
AND ctt.TYPE = al.LOOKUP_CODE
AND al.LOOKUP_TYPE = 'INV/CM'
----------------------------------------------------------
;
SELECT Xmlforest(
ract.Trx_Number AS "ARInvoiceNo"
,Get_Gt_Count
( p_ar_trx_header_id
, p_fp_tax_reg_num
, P_Gt_Inv_Date_From
, P_Gt_Inv_Date_To
, P_Gt_Inv_Num_From
, P_Gt_Inv_Num_To
) AS "Split"
--yao zhang modified for bug8766075
-- ,bat.name AS "ARSource"
,bas.name AS "ARSource"
-- modified by Allen Yang 24-Sep-2009 for bug 8920326
-----------------------------------------------------------------
--,CTT.TYPE AS "ARClass"
,al.meaning AS "ARClass"
-----------------------------------------------------------------
,ar_gta_trx_util.To_Xsd_Date_String(ract.Trx_Date) AS "ARDate"
,RAC_BILL_PARTY.PARTY_NAME AS "ARCustomer"
,'' AS "ARAmount"
,'' AS "ARTaxAmount"
,'' AS "ARTotalAmount"
)
INTO
L_Ret_Xmlelement
FROM
Ra_Customer_Trx_all ract
--yao zhang modified for bug8766075
-- , ra_batches_all bat
,RA_BATCH_SOURCES_ALL bas
, Ra_Cust_Trx_Types_all ctt
, Hz_Cust_Accounts RAC_BILL
, Hz_Parties RAC_BILL_PARTY
-- added by Allen Yang 24-Sep-2009 for bug 8920326
-----------------------------------------------------
, AR_LOOKUPS al
-----------------------------------------------------
WHERE Customer_Trx_Id = P_Ar_Trx_Header_Id
AND ract.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ract.org_id = ctt.org_id
--yao zhang modified for bug8766075
--AND ract.batch_id = bat.batch_id(+)
AND ract.batch_source_id = bas.BATCH_SOURCE_ID(+)
AND ract.org_id =bas.org_id
AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id
-- added by Allen Yang 24-Sep-2009 for bug 8920326
-------------------------------------------------------
AND al.LOOKUP_TYPE = 'INV/CM'
AND ctt.TYPE = al.LOOKUP_CODE
-------------------------------------------------------
;
SELECT
COUNT(*)
INTO
l_count
FROM
ar_gta_trx_headers_all
WHERE Ra_Trx_Id=P_Ar_Trx_Header_Id
AND Source='GT'
AND Status='COMPLETED'
AND Gt_Invoice_Number BETWEEN p_Gt_Inv_Num_From
AND p_Gt_Inv_Num_To
AND Gt_Invoice_Date BETWEEN p_Gt_Inv_Date_From
AND p_Gt_Inv_Date_To
AND Fp_Tax_Registration_Number=NVL(p_Tax_Registration_Number
,Fp_Tax_Registration_Number
);
SELECT Xmlelement("GTInvoices",
Xmlconcat(Xmlelement("Count",l_count)
, Xmlagg(Xmlelement("GTInvoice",Xmlforest
( Gt_Invoice_Number AS "InvoiceNo"
, ar_gta_trx_util.To_Xsd_Date_String(Gt_Invoice_Date) AS "Date"
, Bill_To_Customer_Name AS "Customer"
, Gt_Invoice_Net_Amount AS "Amount"
, Gt_Invoice_Tax_Amount AS "TaxAmount"
, Gt_Invoice_Net_Amount + Gt_Invoice_Tax_Amount AS "TotalAmount"
)
))))
INTO
l_Ret_Xmlelement
FROM
AR_Gta_Trx_Headers_all
WHERE Ra_Trx_Id=P_Ar_Trx_Header_Id
AND Source='GT'
AND Status='COMPLETED'
AND Gt_Invoice_Number BETWEEN p_Gt_Inv_Num_From
AND p_Gt_Inv_Num_To
AND Gt_Invoice_Date BETWEEN p_Gt_Inv_Date_From
AND p_Gt_Inv_Date_To
AND Fp_Tax_Registration_Number=NVL(p_Tax_Registration_Number
,Fp_Tax_Registration_Number
);
SELECT
sysp.gt_currency_code
FROM
ar_gta_system_parameters_all sysp
WHERE sysp.org_id = P_Org_Id;
SELECT
DISTINCT Gt.Ra_Trx_Id
FROM
AR_Gta_Trx_Headers_All Gt
,Ra_Customer_Trx_All Ar
WHERE Gt.Ra_Trx_Id=Ar.Customer_Trx_Id
AND Gt.Org_Id=P_Org_Id
AND Gt.Fp_Tax_Registration_Number=NVL(p_fp_tax_reg_num
,Gt.Fp_Tax_Registration_Number
)
AND Ar.Batch_Source_Id=NVL(P_Trx_Source,Ar.Batch_Source_Id)
AND Ar.Bill_To_Customer_Id = NVL(P_Customer_Id,Ar.Bill_To_Customer_Id)
AND Gt.Source='GT'
AND Gt.Status='COMPLETED'
AND Gt.Gt_Invoice_Number BETWEEN l_Gt_Inv_Num_From
AND l_Gt_Inv_Num_To
AND Gt.Gt_Invoice_Date BETWEEN l_Gt_Inv_Date_From
AND l_Gt_Inv_Date_To
AND Ar.Trx_Number BETWEEN l_Ar_Inv_Num_From
AND l_Ar_Inv_Num_To
AND Ar.Trx_Date BETWEEN l_Ar_Inv_Date_From
AND l_Ar_Inv_Date_To;
SELECT
COUNT(*)
INTO
l_Gt_Rows
FROM
AR_Gta_Trx_Headers Gt
,Ra_Customer_Trx Ar
WHERE Gt.Ra_Trx_Id=Ar.Customer_Trx_Id
AND Gt.Org_Id=P_Org_Id
AND Gt.Fp_Tax_Registration_Number=NVL(p_fp_tax_reg_num
,Gt.Fp_Tax_Registration_Number
)
AND Ar.Batch_Source_Id=NVL(P_Trx_Source,Ar.Batch_Source_Id)
AND Ar.Bill_To_Customer_Id = NVL(P_Customer_Id
,Ar.Bill_To_Customer_Id
)
AND Gt.Source='GT'
AND Gt.Status='COMPLETED'
AND Gt.Gt_Invoice_Number BETWEEN l_Gt_Inv_Num_From
AND l_Gt_Inv_Num_To
AND Gt.Gt_Invoice_Date BETWEEN l_Gt_Inv_Date_From
AND l_Gt_Inv_Date_To
AND Ar.Trx_Number BETWEEN l_Ar_Inv_Num_From
AND l_Ar_Inv_Num_To
AND Ar.Trx_Date BETWEEN l_Ar_Inv_Date_From
AND l_Ar_Inv_Date_To;
SELECT
Xmlconcat(l_ar_trxs,Xmlelement( "Invoice"
, Xmlconcat(L_Ar_Trx,L_Gt_Invoices)))
INTO
l_ar_trxs
FROM
dual;
SELECT
Xmlelement("Parameters",Xmlforest
( ar_gta_trx_util.Get_OperatingUnit(P_Org_Id) AS "OperationUnit"
, p_fp_tax_reg_num AS "TaxRegistrationNumber"
, ar_gta_trx_util.Get_AR_Batch_Source_Name
( P_Org_Id
, P_Trx_Source) AS "TransactionSource"
, ar_gta_trx_util.Get_Customer_Name(P_Customer_Id) AS "ARCustomerName"
, P_Gt_Inv_Num_From AS "GTInvoiceNumFrom"
, P_Gt_Inv_Num_To AS "GTInvoiceNumTo"
, ar_gta_trx_util.To_Xsd_Date_String(P_Gt_Inv_Date_From) AS "GTDateFrom"
, ar_gta_trx_util.To_Xsd_Date_String(P_Gt_Inv_Date_To) AS "GTDateTo"
, P_Ar_Inv_Num_From AS "ARTrxNumberFrom"
, P_Ar_Inv_Num_To AS "ARTrxNumberTo"
, ar_gta_trx_util.To_Xsd_Date_String(P_Ar_Inv_Date_From) AS "ARTrxDateFrom"
, ar_gta_trx_util.To_Xsd_Date_String(P_Ar_Inv_Date_TO) AS "ARTrxDateTo"))
INTO
l_Parameter
FROM DUAL;
SELECT
Xmlelement("Summary", Xmlforest
( L_Ar_Rows AS "NumOfARTrxs"
, L_Gt_Rows AS "NumOfGTInvoices"
)
)
INTO
L_Summary
FROM
DUAL;
SELECT
Xmlelement("RepCurr", l_gt_cur)
INTO
L_gt_currency
FROM
DUAL;
SELECT Xmlelement( "MappingReport", Xmlconcat
( Xmlelement("ReportFailed",'N')
, Xmlelement("FailedWithParameters",'Y')
, Xmlelement("FailedMsgWithParameters",l_no_data_message)
, Xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
, L_Parameter
))
INTO
L_Report
FROM
DUAL;
SELECT Xmlelement( "MappingReport", Xmlconcat
( Xmlelement("ReportFailed",'N')
, Xmlelement("FailedWithParameters",'N')
, Xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
, L_Parameter
, L_Summary
, L_gt_currency
, xmlelement("Invoices",l_ar_trxs)
))
INTO
L_Report
FROM
DUAL;
SELECT
--commented by Donghai due to ebtax functionality
/*AR_GTA_TRX_UTIL.Get_Arinvoice_Amount(rct.customer_trx_id
,rct.invoice_currency_code
,rct.trx_date
,l_org_id) amount
, AR_GTA_TRX_UTIL.Get_Arinvoice_Tax_Amount(rct.customer_trx_id
,rct.invoice_currency_code
,rct.trx_date
,l_org_id) tax_amount*/
rct.bill_to_customer_id
--Yao Zhang modified for bug#8765631
,decode(RAC_BILL_PARTY.Known_As
,null,RAC_BILL_PARTY.PARTY_NAME
,RAC_BILL_PARTY.Known_As) customer_name
--commented by Donghai due to ebtax functionality
--, rac_bill_party.jgzz_fiscal_code taxpayer_id
, 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)) customer_address
FROM
ra_customer_trx_all rct
,hz_parties rac_bill_party
,hz_cust_accounts rac_bill
--,ap_bank_accounts apba
--,ap_bank_branches apb
,hz_cust_site_uses_all su_bill
,hz_party_sites raa_bill_ps
,hz_cust_acct_sites_all raa_bill
,hz_locations raa_bill_loc
,fnd_territories_vl ft_bill
WHERE rct.customer_trx_id=l_ar_header_id
AND rct.bill_to_customer_id=rac_bill.cust_account_id
AND rac_bill.party_id=rac_bill_party.party_id
-- AND rct.customer_bank_account_id=apba.bank_account_id(+)
-- AND apba.bank_branch_id=apb.bank_branch_id(+)
AND rct.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_loc.location_id=raa_bill_ps.location_id
AND raa_bill_loc.country=ft_bill.territory_code(+);
SELECT
/*AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
amount
, AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
taxamount,
*/
gta.bill_to_customer_name
customer_name
, gta.tp_tax_registration_number
tax_registration_number
, gta.bank_account_name_number
customer_bank_account
, gta.customer_address_phone
customer_address_phone
, gta.gta_trx_number
,gta.invoice_type invoice_type --added by subba.
,lk.meaning invoice_type_name --added by subba.
,gta.status--Yao Zhang add for bug#8765631
FROM
ar_gta_trx_headers gta, fnd_lookup_values_vl lk --added by subba.
WHERE gta.ra_trx_id=l_ar_header_id
AND gta.source='AR'
AND (gta.status='COMPLETED' OR gta.status='CONSOLIDATED')--Yao Zhang modified for bug#8765631
AND gta.latest_version_flag='Y'
AND gta.invoice_type = lk.lookup_code --added by subba for R12.1
AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
SELECT
AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gt.gta_trx_header_id)
amount
, AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gt.gta_trx_header_id)
taxamount
, gt.bill_to_customer_name
customer_name
, gt.tp_tax_registration_number
tax_registration_number
, gt.bank_account_name_number
customer_bank_account
, gt.customer_address_phone
customer_address_phone
, gt.gt_invoice_number
, gt.invoice_type invoice_type
, lk.meaning invoice_type_name
FROM
ar_gta_trx_headers gt,
fnd_lookup_values_vl lk --added by Subba for R12.1
WHERE gt.gta_trx_number=l_gta_trx_number
AND gt.source='GT'
AND gt.invoice_type = lk.lookup_code
AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
SELECT
AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
amount
, AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
taxamount
, gta.gta_trx_number
FROM
ar_gta_trx_headers gta
WHERE gta.ra_trx_id=l_ar_header_id
AND gta.source='AR'
AND gta.status<>'CANCELLED'
AND gta.latest_version_flag='Y';
SELECT
flv.meaning
INTO
l_amount_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='AMOUNT';
SELECT
flv.meaning
INTO
l_taxamount_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='VAT_TAX_AMOUNT';
SELECT
flv.meaning
INTO
l_cust_name_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='CUSTOMER_NAME';
SELECT
flv.meaning
INTO
l_bank_name_account_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='BANK_NAME_ACCOUNT';
SELECT
flv.meaning
INTO
l_address_phone_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='ADDRESS_PHONE_NUMBER';
SELECT
flv.meaning
INTO
l_invoicetype_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='INVOICE_TYPE';
/* SELECT
flv.meaning
INTO
l_taxpayer_id_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='TAX_PAYER_ID';*/
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_cust_name_attr
,l_ar_customer_name
,l_gta_trx_number
,l_gta_customer_name
,l_gt_invoice_number
,l_gt_customer_name
,'-'
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy )
VALUES('HEADER'
,l_ar_header_id
,l_invoicetype_attr
,l_gta_invoice_type_name
,l_gta_trx_number
,l_gta_invoice_type_name
,l_gt_invoice_number
,l_gt_invoice_type_name
,'-'
);
SELECT
DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
,'FIRST FOUR VISIBLE', RPAD('*'
,LENGTH(l_ar_customer_bank_account)
,'*'
)
,'LAST FOUR VISIBLE',LPAD('*'
,LENGTHB(l_ar_customer_bank_account)
, '*'
)
, 'NO MASK',l_ar_customer_bank_account
)
INTO
l_ar_customer_bank_account_m
FROM
dual;
SELECT
DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
, 'FIRST FOUR VISIBLE', RPAD('*'
,LENGTH(l_gta_customer_bank_account)
, '*'
)
, 'LAST FOUR VISIBLE', LPAD('*'
,LENGTHB(l_gta_customer_bank_account)
, '*'
)
, 'NO MASK',l_gta_customer_bank_account
)
INTO
l_gta_customer_bank_account_m
FROM
dual;
SELECT
DECODE( NVL(l_ar_mask_bank, 'FIRST FOUR VISIBLE')
, 'FIRST FOUR VISIBLE', RPAD('*'
,LENGTH(l_gt_customer_bank_account)
, '*'
)
, 'LAST FOUR VISIBLE', LPAD('*'
,LENGTHB(l_gt_customer_bank_account)
, '*'
)
, 'NO MASK',l_gt_customer_bank_account
)
INTO
l_gt_customer_bank_account_m
FROM
dual;
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_bank_name_account_attr
,l_ar_customer_bank_account_m
,l_gta_trx_number
,l_gta_customer_bank_account_m
,l_gt_invoice_number
,l_gt_customer_bank_account_m
,'-'
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_address_phone_attr
,l_ar_customer_address_phone
,l_gta_trx_number
,l_gta_customer_address_phone
,l_gt_invoice_number
,l_gt_customer_address_phone
,'-'
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_taxpayer_id_attr
,l_ar_taxpayer_id
,l_gta_trx_number
,l_gta_taxpayer_id
,l_gt_invoice_number
,l_gt_taxpayer_id
,'-'
);
UPDATE
ar_gta_trx_lines_all gta_line
SET
gta_line.matched_flag='N'
WHERE gta_line.gta_trx_header_id=
(SELECT
gta_header.gta_trx_header_id
FROM
ar_gta_trx_headers_all gta_header
WHERE gta_header.source='AR'
AND gta_header.gta_trx_number=l_gta_trx_number
)
AND gta_line.enabled_flag='Y';
UPDATE ar_gta_trx_lines_all gt_line
SET gt_line.matched_flag='N'
WHERE gt_line.gta_trx_header_id=
(SELECT
gt_header.gta_trx_header_id
FROM
ar_gta_trx_headers_all gt_header
WHERE gt_header.source='GT'
AND gt_header.gta_trx_number=l_gta_trx_number
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_amount_attr
,l_ar_amount_disp
,l_gta_trx_number_con
,l_gta_amount_sum_disp
,l_gt_invoice_number_con
,l_gt_amount_sum_disp
,l_amount_discrepancy
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_ar_header_id
,l_taxamount_attr
,l_ar_taxamount_disp
,l_gta_trx_number_con
,l_gta_taxamount_sum_disp
,l_gt_invoice_number_con
,l_gt_taxamount_sum_disp
,l_taxamount_discrepancy
);
SELECT
rctl.customer_trx_line_id
,rctl.line_number
,rctl.description goods_description
,rctl.quantity_invoiced quantity
,rctl.unit_selling_price unit_price
,rctl.uom_code uom
--Yao add for bug#8765631
,rctl.interface_line_context
,rctl.interface_line_attribute1
,rctl.interface_line_attribute6
,rctl.interface_line_attribute11
--Yao add end
FROM
ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id=l_ar_header_id
AND rctl.line_type='LINE';
SELECT
jgth.gta_trx_number
,jgth.tp_tax_registration_number
,jgtl.line_number
,jgtl.item_description
--Yao modified for bug8765631
,(jgtl.amount+nvl(jgtl.discount_amount,0)) amount
,(jgtl.tax_amount+nvl(jgtl.discount_tax_amount,0)) discount_amount
,jgtl.tax_rate
,jgtl.quantity
,round(jgtl.unit_price,2)--Qiong changed from jgtl.unit_price to round(,2) for bug10638369
,jgtl.uom
,jgtl.matched_flag
FROM
ar_gta_trx_headers jgth
,ar_gta_trx_lines_all jgtl
WHERE jgth.ra_trx_id=l_ar_header_id
AND jgth.source='AR'
AND (jgth.status='COMPLETED' OR jgth.status='CONSOLIDATED')
AND jgth.latest_version_flag='Y'
AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
AND jgtl.ar_trx_line_id=l_ar_line_id
AND jgtl.enabled_flag='Y'
ORDER BY jgth.gta_trx_number;
SELECT
COUNT(*)
FROM
ar_gta_trx_headers jgth
,ar_gta_trx_lines_all jgtl
WHERE jgth.ra_trx_id=l_ar_header_id
AND jgth.source='AR'
AND jgth.status='COMPLETED'
AND jgth.latest_version_flag='Y'
AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
AND jgtl.ar_trx_line_id=l_ar_line_id
AND jgtl.enabled_flag='N';
SELECT
jgth.gt_invoice_number
FROM
ar_gta_trx_headers_all jgth
WHERE jgth.source='GT'
AND jgth.gta_trx_number=l_gta_trx_number;
SELECT
jgth.gta_trx_number
,jgtl.line_number
FROM
ar_gta_trx_headers jgth
,ar_gta_trx_lines_all jgtl
WHERE jgth.ra_trx_id=l_ar_header_id
AND jgth.source='AR'
AND jgth.status='COMPLETED'
AND jgth.latest_version_flag='Y'
AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
AND NOT EXISTS (SELECT
rctl.customer_trx_line_id
FROM
ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id=l_ar_header_id
AND rctl.customer_trx_line_id=jgtl.ar_trx_line_id
);
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
rctl.customer_trx_line_id ar_line_id
,rctl.line_number ar_line_num
FROM
ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id=l_ar_header_id
--Yao add to fix bug#8765631 to exclude discount line
AND NOT EXISTS ( SELECT opa.list_line_type_code
FROM oe_price_adjustments opa
WHERE rctl.interface_line_context='ORDER ENTRY'
AND opa.price_adjustment_id = rctl.interface_line_attribute11
AND opa.list_line_type_code='DIS')
AND EXISTS (SELECT
zl.trx_line_id
FROM
zx_lines zl
WHERE zl.application_id = 222
AND zl.trx_id=l_ar_header_id
AND zl.trx_level_type='LINE'
AND zl.entity_code='TRANSACTIONS'
AND zl.trx_line_id=rctl.customer_trx_line_id
AND zl.tax_type_code=l_tax_type_code
AND zl.tax_currency_code=l_gt_currency_code
AND zl.event_class_code IN ('INVOICE'
,'CREDIT_MEMO'
,'DEBIT_MEMO'
)
)
AND NOT EXISTS (SELECT
jgtl.ar_trx_line_id
FROM
ar_gta_trx_headers jgth
,ar_gta_trx_lines_all jgtl
WHERE jgth.ra_trx_id=l_ar_header_id
AND jgth.source='AR'
AND jgtl.gta_trx_header_id=jgth.gta_trx_header_id
AND jgtl.ar_trx_line_id=rctl.customer_trx_line_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 exchange_rate into l_conversion_rate
from ra_customer_trx_all
where customer_trx_id=l_ar_header_id;
SELECT
flv.meaning
INTO
l_goods_description_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='GOODS_DESCRIPTION';
SELECT
flv.meaning
INTO
l_line_amount_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='LINE_AMOUNT';
SELECT
flv.meaning
INTO
l_vat_line_tax_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='VAT_TAX_AMOUNT';
SELECT
flv.meaning
INTO
l_vat_tax_rate_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='VAT_TAX_RATE';
SELECT
flv.meaning
INTO
l_quantity_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='QUANTITY';
SELECT
flv.meaning
INTO
l_unit_price_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='UNIT_PRICE';
SELECT
flv.meaning
INTO
l_uom_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='UOM';
SELECT
flv.meaning
INTO
l_tax_reg_number_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='TAX_REGISTRATION_NUMBER';
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 rctl.revenue_amount + nvl(l_discount_amount, 0),
rctl.customer_trx_line_id
INTO l_discount_amount, l_discount_cust_trx_line_id
FROM ra_customer_trx_lines_all rctl
WHERE rctl.customer_trx_id = l_ar_header_id
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 =
l_discount_adjustment_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 application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = l_gt_currency_code
AND tax.tax_type_code = l_tax_type_code
AND tax.trx_id = l_ar_header_id;
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_tax_reg_number_attr
,l_ar_line_number
,l_ar_tax_reg_number
,l_gta_trx_number
,l_gta_line_number
,l_gta_tax_reg_number
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_goods_description_attr
,l_ar_line_number
,l_ar_goods_description
,l_gta_trx_number
,l_gta_line_number
,l_gta_goods_description
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_vat_tax_rate_attr
,l_ar_line_number
,l_ar_vat_tax_rate
,l_gta_trx_number
,l_gta_line_number
,l_gta_vat_tax_rate
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_unit_price_attr
,l_ar_line_number
,l_ar_unit_price_disp
,l_gta_trx_number
,l_gta_line_number
,l_gta_unit_price_disp
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_uom_attr
,l_ar_line_number
,l_ar_uom
,l_gta_trx_number
,l_gta_line_number
,l_gta_uom
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_quantity_attr
,l_ar_line_number
,l_ar_quantity
,l_gta_trx_number_con
,l_gta_line_number_con
,l_gta_line_quantity_sum
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_line_amount_attr
,l_ar_line_number
,l_ar_line_amount_disp
,l_gta_trx_number_con
,l_gta_line_number_con
,l_gta_line_amount_sum_disp
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ATTRIBUTE
,ar_line_num
,ar_value
,gta_invoice_num
,gta_line_num
,gta_value
,gt_invoice_num
,gt_value
)
VALUES('LINE'
,l_ar_header_id
,l_ar_line_id
,l_vat_line_tax_attr
,l_ar_line_number
,l_ar_vat_line_tax_disp
,l_gta_trx_number_con
,l_gta_line_number_con
,l_gta_line_taxamount_sum_disp
,l_gt_invoice_number
,l_gt_value
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_num
,gta_invoice_num
,gta_line_num
,gt_invoice_num
)
VALUES('MISSING_AR_LINE'
,l_ar_header_id
,l_no_value
,l_gta_trx_number
,l_gta_line_number
,l_gt_invoice_number
);
INSERT INTO ar_gta_difference_temp(TYPE
,ar_header_id
,ar_line_id
,ar_line_num
,gta_invoice_num
,gta_line_num
,gt_invoice_num
)
VALUES('MISSING_AR_LINE'
,l_ar_header_id
,l_not_transferred_ar_line.ar_line_id
,l_not_transferred_ar_line.ar_line_num
,l_no_value
,l_no_value
,l_no_value
);
SELECT
gta_header.gta_trx_number
,gta_header.tp_tax_registration_number
,gta_line.line_number
,gta_line.item_description
,gta_line.item_model
,gta_line.unit_price
,gta_line.quantity
,gta_line.uom_name
,gta_line.amount
,gta_line.tax_amount
,gta_line.tax_rate
FROM
ar_gta_trx_headers gta_header
,ar_gta_trx_lines gta_line
WHERE gta_header.ra_trx_id=l_ar_header_id
AND gta_header.source='AR'
AND gta_header.status='COMPLETED'
AND gta_header.latest_version_flag='Y'
AND gta_line.gta_trx_header_id=gta_header.gta_trx_header_id
AND gta_line.enabled_flag='Y'
AND gta_line.matched_flag='N';
SELECT
gt_header.gt_invoice_number
,gt_header.tp_tax_registration_number
,gt_line.line_number
,gt_line.item_description
,gt_line.item_model
,gt_line.unit_price
,gt_line.quantity
,gt_line.uom_name
,gt_line.amount
,gt_line.tax_amount
,gt_line.tax_rate
FROM
ar_gta_trx_headers gt_header
,ar_gta_trx_lines gt_line
WHERE gt_header.ra_trx_id=l_ar_header_id
AND gt_header.source='GT'
AND gt_line.gta_trx_header_id=gt_header.gta_trx_header_id
AND gt_line.matched_flag='N'
--Yao Zhang add for bug#8765631
AND gt_line.discount_flag='0';
INSERT INTO ar_gta_unmatched_temp(source
,ar_header_id
,invoice_number
,tp_tax_registration_number
,line_number
,item_name
,model
,unit_price
,quantity
,uom
,line_amount
,tax_rate
,vat_line_tax
)
VALUES(l_source_gta
,l_ar_header_id
,l_gta_unmatched_line.gta_trx_number
,l_gta_unmatched_line.tp_tax_registration_number
,l_gta_unmatched_line.line_number
,l_gta_unmatched_line.item_description
,l_gta_unmatched_line.item_model
,l_gta_unmatched_line.unit_price
,l_gta_unmatched_line.quantity
,l_gta_unmatched_line.uom_name
,l_gta_unmatched_line.amount
,l_gta_unmatched_line.tax_rate
,l_gta_unmatched_line.tax_amount
);
INSERT INTO ar_gta_unmatched_temp(source
,ar_header_id
,invoice_number
,tp_tax_registration_number
,line_number
,item_name
,model
,unit_price
,quantity
,uom
,line_amount
,tax_rate
,vat_line_tax
)
VALUES(l_source_gt
,l_ar_header_id
,l_gt_unmatched_line.gt_invoice_number
,l_gt_unmatched_line.tp_tax_registration_number
,l_gt_unmatched_line.line_number
,l_gt_unmatched_line.item_description
,l_gt_unmatched_line.item_model
,l_gt_unmatched_line.unit_price
,l_gt_unmatched_line.quantity
,l_gt_unmatched_line.uom_name
,l_gt_unmatched_line.amount
,l_gt_unmatched_line.tax_rate
,l_gt_unmatched_line.tax_amount
);
SELECT
otl.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 = p_org_id;
SELECT
name
FROM
ra_cust_trx_types_all
WHERE cust_trx_type_id=p_ar_transaction_type;
SELECT
hp.party_name
FROM
hz_cust_accounts hca
,hz_parties hp
WHERE hca.cust_account_id=l_customer_id
AND hp.party_id=hca.party_id;
SELECT
name
FROM
ra_salesreps_all
WHERE salesrep_id=p_primary_sales;
SELECT
count(*)
FROM
ar_gta_ar_difference_temp;
SELECT
COUNT(*)
FROM
ar_gta_missing_artrx_temp;
SELECT
adt.customer_trx_id
,adt.trx_number
,adt.trx_date
,adt.customer_name
,adt.invoice_currency_code
FROM
ar_gta_ar_difference_temp adt;
SELECT
COUNT(DISTINCT ar_line_id)
FROM
ar_gta_difference_temp
WHERE TYPE='LINE';
SELECT XMLELEMENT("Difference"
,XMLFOREST(attribute AS "ARAttribute"
,ar_value AS "ARValue"
,gta_invoice_num AS "GTAInvoiceNum"
,gta_value AS "GTAValue"
,gt_invoice_num AS "GT_InvoiceNum"
,gt_value AS "GT_Value"
,discrepancy AS "Discrepancy"
)
)
FROM
ar_gta_difference_temp
WHERE ar_header_id=l_ar_header_id
AND type='HEADER';
SELECT
XMLELEMENT("Difference"
,XMLFOREST(attribute AS "ARAttribute"
,ar_line_num AS "ARLineNum"
,ar_value AS "ARValue"
,gta_invoice_num AS "GTAInvoiceNum"
,gta_line_num AS "GTALineNum"
,gta_value AS "GTAValue"
,gt_invoice_num AS "GT_InvoiceNum"
,gt_line_num AS "GT_LineNum"
,gt_value AS "GT_Value"
)
)
FROM
ar_gta_difference_temp
WHERE ar_header_id=l_ar_header_id
AND type='LINE';
SELECT
XMLELEMENT("Difference"
,XMLFOREST(ar_line_num AS "ARLineNum"
,gta_invoice_num AS "GTAInvoiceNum"
,gta_line_num AS "GTALineNum"
,gt_invoice_num AS "GT_InvoiceNum"
)
)
FROM
ar_gta_difference_temp
WHERE ar_header_id=l_ar_header_id
AND TYPE='MISSING_AR_LINE';
SELECT
XMLELEMENT("Line"
,XMLFOREST(source AS "Source"
,invoice_number AS "InvoiceNum"
,tp_tax_registration_number AS "TaxRegNum"
,line_number AS "LineNum"
,item_name AS "ItemName"
,model AS "Model"
,unit_price AS "UnitPrice"
,quantity AS "Quantity"
,uom AS "UOM"
,line_amount AS "LineAmount"
,tax_rate AS "TaxRate"
,vat_line_tax AS "VatLineTax"
)
)
FROM
ar_gta_unmatched_temp
WHERE ar_header_id=l_ar_header_id
ORDER BY source DESC;
SELECT
XMLELEMENT("Invoice"
,XMLFOREST(record_number AS "InvoiceNumber"
,ar_trx_number AS "OriginalARTrxNum"
,gta_trx_number AS "GTAInvoiceNumber"
,gt_invoice_number AS "GT_InvoiceNum"
,gt_invoice_amount AS "GT_InvoiceAmount"
)
)
FROM
ar_gta_missing_artrx_temp;
SELECT
gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE
org_id=p_org_id;
SELECT
xmlforest( l_operating_unit AS "OperationUnit"
,l_gta_batch_num_from AS "BatchNumFrom"
,l_gta_batch_num_to AS "BatchNumTo"
,l_ar_transaction_type AS "TransactionType"
,l_cust_num_from AS "CustomerNumberFrom"
,l_cust_num_to AS "CustomerNumberTo"
,l_customer_name AS "CustomerName"
,l_gl_period AS "GLPeriod"
,l_gl_date_from_f AS "GLDateFrom"
,l_gl_date_to_f AS "GLDateTo"
,l_ar_trx_batch_from AS "TransactionBatchFrom"
,l_ar_trx_batch_to AS "TransactionBatchTo"
,l_ar_trx_num_from AS "TransactionNumberFrom"
,l_ar_trx_num_to AS "TransactionNumberTo"
,l_ar_trx_date_from_f AS "TransactionDateFrom"
,l_ar_trx_date_to_f AS "TransactionDateTo"
,l_ar_doc_num_from AS "DocNumberFrom"
,l_ar_doc_num_to AS "DocNumberTo"
,l_original_curr_code AS "OriginalCurrency"
,l_primary_sales_name AS "PrimarySalesPerson"
)
INTO
l_parameter_xml
FROM dual;
SELECT
xmlelement("DiscrepancyReport"
,xmlforest(l_report_date AS "RepDate"
,'N' AS "ReportFailed"
,l_parameter_xml AS "Parameters"
,'Y' AS "FailedWithParameters"
,l_no_data_found_msg AS "FailedMsgWithParameters"
)
)
INTO
l_report_xml
FROM
dual;
SELECT
XMLFOREST(l_validated_lines_total AS "NumOfARLines"
,l_ar_matching_lines_total AS "NumOfLinesMatch"
,l_ar_line_notmatching_total AS "NumOfLinesNoMatch"
,l_ar_partially_import_total AS "NumOfNotToGT"
,l_ar_missingtrx_total AS "NumOfMissingAR"
)
INTO
l_summary_xml
FROM
dual;
SELECT COUNT(*)
INTO l_consolidate_count
FROM ar_gta_trx_headers
WHERE ra_trx_id = l_ar_header_id
AND status = 'CONSOLIDATED';
SELECT
XMLCONCAT(l_gta_header_xml
,l_gta_header_xml_tmp
)
INTO
l_gta_header_xml
FROM
dual;
SELECT
XMLCONCAT(l_gta_line_xml
,l_gta_line_xml_tmp
)
INTO
l_gta_line_xml
FROM
dual;
SELECT
XMLCONCAT(l_missing_line_xml
,l_missing_line_xml_tmp
)
INTO
l_missing_line_xml
FROM
dual;
SELECT
XMLCONCAT(l_unmatched_line_xml
,l_unmatched_line_xml_tmp
)
INTO
l_unmatched_line_xml
FROM
dual;
SELECT
XMLELEMENT("Invoice"
,XMLFOREST
(l_ar_difference.trx_number AS "ARInvoiceNo"
,l_ar_trx_date AS "ARDate"
,l_ar_difference.customer_name AS "Customer"
,l_ar_difference.invoice_currency_code AS "OriginalCurrency"
,l_description AS "Description"--Yao Zhang add for bug#8765631
,l_gta_header_xml AS "HeaderLevel"
,l_gta_line_xml AS "LineLevel"
,l_missing_line_xml AS "MissARLine"
,l_unmatched_line_xml AS "UnmatchedLines"
)
)
INTO l_ar_invoice_xml_tmp
FROM
dual;
SELECT
XMLCONCAT(l_ar_invoice_xml
,l_ar_invoice_xml_tmp
)
INTO
l_ar_invoice_xml
FROM dual;
SELECT
XMLCONCAT(l_missing_artrx_xml
,l_missing_artrx_xml_tmp
)
INTO
l_missing_artrx_xml
FROM
dual;
SELECT
XMLELEMENT("DiscrepancyReport"
,XMLFOREST(l_report_date AS "RepDate"
,l_no_char AS "ReportFailed"
,l_parameter_xml AS "Parameters"
,l_no_char AS "FailedWithParameters"
,l_summary_xml AS "Summary"
,l_base_currency AS "RepCurr"
,l_ar_invoice_xml AS "Invoices"
,l_missing_artrx_xml AS "MissingInvoices"
)
)
INTO
l_report_xml
FROM
dual;
SELECT DISTINCT
gta.ra_trx_id
,ct.trx_number
,ct.trx_date
,rac_bill_party.party_name customer_name
,ct.invoice_currency_code
FROM
ar_gta_trx_headers gta
,ra_customer_trx_all ct
,hz_cust_accounts rac_bill
,hz_parties rac_bill_party
,ra_cust_trx_line_gl_dist_all gd
,ra_batches_all rb
WHERE gta.ra_trx_id=ct.customer_trx_id(+)
AND rb.batch_id(+)=ct.batch_id
AND gta.source='AR'
AND(gta.status='COMPLETED' OR gta.status='CONSOLIDATED')--Yao Zhang Modified for bug#8765631
AND gta.latest_version_flag='Y'
AND gta.org_id=l_org_id
AND ((gta.gta_batch_number>=l_gta_batch_num_from) OR
(l_gta_batch_num_from IS NULL))
AND ((gta.gta_batch_number<=l_gta_batch_num_to) OR
(l_gta_batch_num_to IS NULL))
AND ((ct.cust_trx_type_id=l_ar_transaction_type) OR
(l_ar_transaction_type IS NULL))
AND ct.bill_to_customer_id=rac_bill.cust_account_id(+)
AND rac_bill.party_id = rac_bill_party.party_id(+)
AND ((rac_bill.account_number>=l_cust_num_from) OR
(l_cust_num_from IS NULL))
AND ((rac_bill.account_number<=l_cust_num_to) OR
(l_cust_num_to IS NULL))
AND ((ct.bill_to_customer_id=l_cust_id) OR
(l_cust_id IS NULL))
AND ((gta.ra_gl_period=l_gl_period) OR
(l_gl_period IS NULL))
AND ct.customer_trx_id = gd.customer_trx_id(+)
AND 'REC' = gd.account_class(+)
AND 'Y' = gd.latest_rec_flag(+)
AND ((gd.gl_date>=l_gl_date_from_d) OR
(l_gl_date_from_d IS NULL ))
AND ((gd.gl_date<=l_gl_date_to_d) OR
(l_gl_date_to_d IS NULL))
AND ((rb.name>=l_ar_trx_batch_from) OR
(l_ar_trx_batch_from IS null))
AND ((rb.name<=l_ar_trx_batch_to) OR
(l_ar_trx_batch_to IS NULL))
AND ((ct.trx_number>=l_ar_trx_num_from) OR
(l_ar_trx_num_from IS NULL))
AND ((ct.trx_number<=l_ar_trx_num_to) OR
(l_ar_trx_num_to IS NULL))
AND ((ct.trx_date>=l_ar_trx_date_from_d) OR
(l_ar_trx_date_from_d IS NULL))
AND ((ct.trx_date<=l_ar_trx_date_to_d) OR
(l_ar_trx_date_to_d IS NULL))
AND ((ct.doc_sequence_value>=l_ar_doc_num_from) OR
(l_ar_doc_num_from IS NULL))
AND ((ct.doc_sequence_value<=l_ar_doc_num_to) OR
(l_ar_doc_num_to IS NULL))
AND ((ct.invoice_currency_code=l_original_curr_code) OR
(l_original_curr_code IS NULL))
AND ((ct.primary_salesrep_id=l_primary_sales) OR
(l_primary_sales IS null))
--Yao Zhang add for bug#8765631
AND (gta.consolidation_flag IS NULL OR gta.consolidation_flag='1');
SELECT
gth.gta_trx_number
,gth.ra_trx_number
,gth.gt_invoice_number
,gth.gta_trx_header_id
FROM
ar_gta_trx_headers gth
WHERE gth.SOURCE='AR'--Yao Zhang add for bug#8765631
AND (gth.status='COMPLETED' OR gth.status='CONSOLIDATED')
AND gth.ra_trx_id=l_ar_trx_header_id;
SELECT
DISTINCT gth.ra_trx_number
FROM
ar_gta_trx_headers gth
WHERE gth.SOURCE='AR'--Yao Zhang add for bug#8765631
AND (gth.status='COMPLETED' OR gth.status='CONSOLIDATED')
AND gth.ra_trx_id=l_ar_trx_header_id;
SELECT gta.gta_trx_header_id
,gta.gta_trx_number
,gta.ra_gl_date
,gta.BILL_TO_CUSTOMER_NAME customer_name
FROM ar_gta_trx_headers gta
WHERE gta.consolidation_flag = '0'
AND gta.status = 'COMPLETED'
AND gta.SOURCE = 'AR'
AND gta.org_id = l_org_id
AND gta.latest_version_flag = 'Y'
AND ((gta.gta_batch_number >= l_gta_batch_num_from) OR
(l_gta_batch_num_from IS NULL))
AND ((gta.gta_batch_number <= l_gta_batch_num_to) OR
(l_gta_batch_num_to IS NULL))
AND l_ar_transaction_type IS NULL
AND ((gta.BILL_TO_CUSTOMER_NUMBER>= l_cust_num_from) OR
(l_cust_num_from IS NULL))
AND ((gta.BILL_TO_CUSTOMER_NUMBER <= l_cust_num_to) OR
(l_cust_num_to IS NULL))
AND ((gta.BILL_TO_CUSTOMER_ID = l_cust_id) OR (l_cust_id IS NULL))
AND ((gta.ra_gl_period = l_gl_period) OR (l_gl_period IS NULL))
AND ((gta.ra_gl_date >= l_gl_date_from_d) OR (l_gl_date_from_d IS NULL))
AND ((gta.ra_gl_date <= l_gl_date_to_d) OR (l_gl_date_to_d IS NULL))
AND l_ar_trx_batch_from IS NULL
AND l_ar_trx_batch_to IS NULL
AND l_ar_trx_num_from IS NULL
AND l_ar_trx_num_to IS NULL
AND l_ar_trx_date_from_d IS NULL
AND l_ar_trx_date_to_d IS NULL
AND l_ar_doc_num_from IS NULL
AND l_ar_doc_num_to IS NULL
AND l_original_curr_code IS NULL
AND l_primary_sales IS NULL;
INSERT INTO ar_gta_missing_artrx_temp(record_number
,gta_trx_number
,ar_trx_number
,gt_invoice_number
,gt_invoice_amount
)
VALUES(l_missing_artrx_seq
,l_gta_trx_number_missing
,l_ar_trx_number_missing
,l_gt_invoice_number_missing
,l_gt_invoice_amount_missing
);
INSERT INTO ar_gta_ar_difference_temp(customer_trx_id
,trx_number
,trx_date
,customer_name
,invoice_currency_code
)
VALUES(l_ar_trx_header_id
,l_ar_trx_number
,l_ar_trx_date
,l_ar_customer_name
,l_ar_currency_code
);
INSERT INTO ar_gta_ar_difference_temp(customer_trx_id
,trx_number
,trx_date
,customer_name
,invoice_currency_code
)
VALUES(l_consolidated_inv.gta_trx_header_id
,l_consolidated_inv.gta_trx_number
,l_consolidated_inv.ra_gl_date
,l_consolidated_inv.customer_name
,NULL
);
SELECT
GTA_TRX_HEADER_ID
FROM
AR_Gta_Trx_Headers_All
WHERE ORG_ID = p_org_id
AND RA_GL_PERIOD = NVL(p_gl_period, RA_GL_PERIOD)
AND SOURCE = 'AR'
AND STATUS NOT IN ('CANCELLED') --fix bug 14078699
AND CONSOLIDATION_FLAG = '0'
AND BILL_TO_CUSTOMER_NUMBER BETWEEN NVL(l_customer_num_from
,BILL_TO_CUSTOMER_NUMBER)
AND NVL(l_customer_num_to
,BILL_TO_CUSTOMER_NUMBER)
AND BILL_TO_CUSTOMER_NAME BETWEEN NVL(l_customer_name_from
,BILL_TO_CUSTOMER_NAME)
AND NVL(l_customer_name_to
,BILL_TO_CUSTOMER_NAME)
AND GTA_TRX_NUMBER BETWEEN NVL(l_consol_trx_num_from, GTA_TRX_NUMBER)
AND NVL(l_consol_trx_num_to, GTA_TRX_NUMBER)
AND INVOICE_TYPE = NVL(l_invoice_type, INVOICE_TYPE)
ORDER BY
BILL_TO_CUSTOMER_NAME
,TP_TAX_REGISTRATION_NUMBER
,INVOICE_TYPE
,RA_GL_PERIOD
,CUSTOMER_ADDRESS_PHONE
,BANK_ACCOUNT_NAME
,BANK_ACCOUNT_NUMBER
,GTA_TRX_NUMBER;
SELECT
COUNT(*)
INTO
l_consolidation_rows
FROM
AR_Gta_Trx_Headers
WHERE ORG_ID = p_org_id
AND RA_GL_PERIOD = NVL(p_gl_period, RA_GL_PERIOD)
AND Source = 'AR'
AND STATUS NOT IN ('CANCELLED') --fix bug 14078699
AND CONSOLIDATION_FLAG = '0'
AND BILL_TO_CUSTOMER_NUMBER BETWEEN NVL(l_customer_num_from
, BILL_TO_CUSTOMER_NUMBER)
AND NVL(l_customer_num_to
, BILL_TO_CUSTOMER_NUMBER)
AND BILL_TO_CUSTOMER_NAME BETWEEN NVL(l_customer_name_from
, BILL_TO_CUSTOMER_NAME)
AND NVL(l_customer_name_to
, BILL_TO_CUSTOMER_NAME)
AND CONSOLIDATION_TRX_NUM BETWEEN NVL(l_consol_trx_num_from
, CONSOLIDATION_TRX_NUM)
AND NVL(l_consol_trx_num_to
, CONSOLIDATION_TRX_NUM)
AND INVOICE_TYPE = NVL(l_invoice_type, INVOICE_TYPE);
SELECT
Xmlconcat(l_consolidation_trxs
, Xmlelement("Invoice"
, Xmlconcat(l_consolidation_trx
, l_consolidated_trxs)))
INTO
l_consolidation_trxs
FROM DUAL;
SELECT meaning
INTO l_invoice_type_disp
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = 'AR_GTA_INVOICE_TYPE'
AND lookup_code = p_invoice_type;
SELECT
Xmlelement("Parameters"
, Xmlforest( ar_gta_trx_util.Get_OperatingUnit(p_org_id)
AS "OperationUnit"
, p_gl_period AS "GLPeriod"
, p_customer_num_from AS "CustNumFrom"
, p_customer_num_to AS "CustNumTo"
, p_customer_name_from AS "CustNameFrom"
, p_customer_name_to AS "CustNameTo"
, p_consol_trx_num_from AS "ConsolTrxNumFrom"
, p_consol_trx_num_to AS "ConsolTrxNumTo"
-- modified by Allen Yang 02-Sep-2009 for bug 8848696
--, p_invoice_type AS "InvoiceType"))
, l_invoice_type_disp AS "InvoiceType"))
-- end modified by Allen Yang
INTO
l_parameter
FROM DUAL;
SELECT Xmlelement("ConsolidationMappingReport"
, Xmlconcat(Xmlelement("ReportFailed",'N')
, Xmlelement("FailedWithParameters",'Y')
, Xmlelement("FailedMsgWithParameters"
, l_no_data_message)
, Xmlelement("RepDate"
, ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
, l_parameter))
INTO
l_report
FROM DUAL;
SELECT Xmlelement("ConsolidationMappingReport"
, Xmlconcat(Xmlelement("ReportFailed",'N')
, Xmlelement("FailedWithParameters",'N')
, Xmlelement("RepDate"
,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
, l_parameter
, xmlelement("Invoices", l_consolidation_trxs)))
INTO
l_report
FROM DUAL;
SELECT Xmlforest(
GTA_TRX_NUMBER AS "ConsolidationTrxNum"
, CUSTOMER_ADDRESS_PHONE AS "CustAddressPhone"
, RA_GL_PERIOD AS "GLPeriod"
, BILL_TO_CUSTOMER_NAME AS "CustomerName"
, TP_TAX_REGISTRATION_NUMBER AS "TaxRegistrationNum"
, INVOICE_TYPE AS "InvoiceType"
, BANK_ACCOUNT_NAME AS "BankName"
, BANK_ACCOUNT_NUMBER AS "BankAccountNum"
-- modified by Allen Yang 25-Aug-2009 for bug 8809860
--, ar_gta_trx_util.Get_Gtainvoice_Amount(p_header_id =>
-- p_trx_header_id) AS "Amount")
, ar_gta_trx_util.Get_Gtainvoice_Amount(p_trx_header_id) AS "Amount")
-- end modified by Allen
INTO
l_ret_xmlelement
FROM AR_GTA_TRX_HEADERS_ALL
WHERE GTA_TRX_HEADER_ID = p_trx_header_id;
SELECT Xmlforest(
JGTHA.GTA_TRX_NUMBER AS "ConsolidationTrxNum"
, JGTHA.CUSTOMER_ADDRESS_PHONE AS "CustAddressPhone"
, JGTHA.RA_GL_PERIOD AS "GLPeriod"
, JGTHA.BILL_TO_CUSTOMER_NAME AS "CustomerName"
, JGTHA.TP_TAX_REGISTRATION_NUMBER AS "TaxRegistrationNum"
, FLTV.MEANING AS "InvoiceType"
, JGTHA.BANK_ACCOUNT_NAME AS "BankName"
, JGTHA.BANK_ACCOUNT_NUMBER AS "BankAccountNum"
-- modified by Allen Yang 25-Aug-2009 for bug 8809860
--, ar_gta_trx_util.Get_Gtainvoice_Amount(p_header_id =>
-- p_trx_header_id) AS "Amount")
, ar_gta_trx_util.Get_Gtainvoice_Amount(p_trx_header_id) AS "Amount")
-- end modified by Allen
INTO
l_ret_xmlelement
FROM AR_GTA_TRX_HEADERS_ALL JGTHA
, FND_LOOKUP_VALUES_VL FLTV
WHERE GTA_TRX_HEADER_ID = p_trx_header_id
AND FLTV.LOOKUP_TYPE = 'AR_GTA_INVOICE_TYPE'
AND JGTHA.INVOICE_TYPE = FLTV.LOOKUP_CODE;
SELECT Xmlelement("ConsolidatedInvoices"
, Xmlagg(Xmlelement("ConsolidatedInvoice"
,Xmlforest(jgtha.RA_GL_PERIOD AS "ConsolidatedGLPeriod"
,jgtha.GTA_TRX_NUMBER AS "ConsolidatedTrxNum"
,jgtha.RA_TRX_NUMBER AS "ARTrxNum"
,rctt.NAME AS "ARTrxType"
,ar_gta_trx_util.Get_Gtainvoice_Amount
-- modified by Allen Yang 25-Aug-2009 for bug 8809860
--(p_header_id =>p_trx_header_id)
(jgtha.GTA_TRX_HEADER_ID)
-- end modified by Allen
AS "ConsolidatedAmount"))))
INTO
l_ret_xmlelement
FROM
AR_Gta_Trx_Headers_all jgtha
, ra_customer_trx_all rcta
, ra_cust_trx_types rctt
WHERE jgtha.CONSOLIDATION_FLAG = '1'
AND jgtha.SOURCE = 'AR'
AND jgtha.Status='CONSOLIDATED'
AND jgtha.CONSOLIDATION_TRX_NUM =
(SELECT GTA_TRX_NUMBER
FROM AR_Gta_Trx_Headers_all
WHERE GTA_TRX_HEADER_ID = p_trx_header_id)
AND rctt.CUST_TRX_TYPE_ID = rcta.CUST_TRX_TYPE_ID
AND rcta.CUSTOMER_TRX_ID = jgtha.RA_TRX_ID;
SELECT
AR_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
amount
, AR_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
taxamount
, gta.bill_to_customer_name
customer_name
, gta.tp_tax_registration_number
tax_registration_number
, gta.bank_account_name_number
customer_bank_account
, gta.customer_address_phone
customer_address_phone
, gta.gta_trx_number
,gta.invoice_type invoice_type
,lk.meaning invoice_type_name
FROM
ar_gta_trx_headers gta, fnd_lookup_values_vl lk
WHERE gta.gta_trx_header_id=l_gta_header_id
AND gta.invoice_type = lk.lookup_code
AND lk.lookup_type='AR_GTA_INVOICE_TYPE';
SELECT ar_gta_trx_util.get_gtainvoice_amount(gt.gta_trx_header_id) amount,
ar_gta_trx_util.get_gtainvoice_tax_amount(gt.gta_trx_header_id) taxamount,
gt.bill_to_customer_name customer_name,
gt.tp_tax_registration_number tax_registration_number,
gt.bank_account_name_number customer_bank_account,
gt.customer_address_phone customer_address_phone,
gt.gt_invoice_number,
gt.invoice_type invoice_type,
lk.meaning invoice_type_name
FROM ar_gta_trx_headers gt, fnd_lookup_values_vl lk
WHERE gt.gta_trx_number IN
(SELECT gta_trx_number
FROM ar_gta_trx_headers gta
WHERE gta.gta_trx_header_id = l_gta_header_id)
AND gt.SOURCE = 'GT'
AND gt.invoice_type = lk.lookup_code
AND lk.lookup_type = 'AR_GTA_INVOICE_TYPE';
SELECT
flv.meaning
INTO
l_amount_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='AMOUNT';
SELECT
flv.meaning
INTO
l_taxamount_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='VAT_TAX_AMOUNT';
SELECT
flv.meaning
INTO
l_cust_name_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='CUSTOMER_NAME';
SELECT
flv.meaning
INTO
l_bank_name_account_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='BANK_NAME_ACCOUNT';
SELECT
flv.meaning
INTO
l_address_phone_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='ADDRESS_PHONE_NUMBER';
SELECT
flv.meaning
INTO
l_invoicetype_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='AR_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='INVOICE_TYPE';
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_amount_attr
,NULL
,l_gta_trx_number
,l_gta_amount
,l_gt_invoice_number
,l_gt_amount
,l_gta_amount-l_gt_amount
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_taxamount_attr
,NULL
,l_gta_trx_number
,l_gta_taxamount
,l_gt_invoice_number
,l_gt_taxamount
,l_gta_taxamount-l_gt_taxamount
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_cust_name_attr
,NULL
,l_gta_trx_number
,l_gta_customer_name
,l_gt_invoice_number
,l_gt_customer_name
,NULL
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_taxpayer_id_attr
,NULL
,l_gta_trx_number
,l_gta_taxpayer_id
,l_gt_invoice_number
,l_gt_taxpayer_id
,NULL
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_bank_name_account_attr
,NULL
,l_gta_trx_number
,l_gta_customer_bank_account
,l_gt_invoice_number
,l_gt_customer_bank_account
,NULL
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_address_phone_attr
,NULL
,l_gta_trx_number
,l_gta_customer_address_phone
,l_gt_invoice_number
,l_gt_customer_address_phone
,NULL
);
INSERT INTO ar_gta_difference_temp(type
,ar_header_id
,attribute
,ar_value
,gta_invoice_num
,gta_value
,gt_invoice_num
,gt_value
,discrepancy
)
VALUES('HEADER'
,l_gta_header_id
,l_invoicetype_attr
,NULL
,l_gta_trx_number
,l_gta_invoice_type_name
,l_gt_invoice_number
,l_gt_invoice_type_name
,NULL
);