[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
COUNT(*)
INTO
l_count
FROM
Jmf_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"
,bat.name AS "ARSource"
,CTT.TYPE AS "ARClass"
,jmf_gta_trx_util.To_Xsd_Date_String(ract.Trx_Date) AS "ARDate"
,RAC_BILL_PARTY.PARTY_NAME AS "ARCustomer"
,Jmf_Gta_Trx_Util.Get_Arinvoice_Amount
( P_Org_Id
, ract.Customer_Trx_Id
) AS "ARAmount"
,Jmf_Gta_Trx_Util.Get_Arinvoice_Tax_Amount
( P_Org_Id
, ract.Customer_Trx_Id
) AS "ARTaxAmount"
,Jmf_Gta_Trx_Util.Get_Arinvoice_Amount
( P_Org_Id
, ract.Customer_Trx_Id
)
+Jmf_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
, ra_batches_all bat
, Ra_Cust_Trx_Types_all ctt
, Hz_Cust_Accounts RAC_BILL
, Hz_Parties RAC_BILL_PARTY
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
AND ract.batch_id = bat.batch_id(+)
AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id;
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"
,bat.name AS "ARSource"
,CTT.TYPE AS "ARClass"
,jmf_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
, ra_batches_all bat
, Ra_Cust_Trx_Types_all ctt
, Hz_Cust_Accounts RAC_BILL
, Hz_Parties RAC_BILL_PARTY
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
AND ract.batch_id = bat.batch_id(+)
AND ract.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.party_id = RAC_BILL_PARTY.Party_Id;
SELECT
COUNT(*)
INTO
l_count
FROM
jmf_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"
, jmf_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
Jmf_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
jmf_gta_system_parameters_all sysp
WHERE sysp.org_id = P_Org_Id;
SELECT
DISTINCT Gt.Ra_Trx_Id
FROM
Jmf_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
Jmf_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
( jmf_gta_trx_util.Get_OperatingUnit(P_Org_Id) AS "OperationUnit"
, p_fp_tax_reg_num AS "TaxRegistrationNumber"
, jmf_gta_trx_util.Get_AR_Batch_Source_Name
( P_Org_Id
, P_Trx_Source) AS "TransactionSource"
, jmf_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"
, jmf_gta_trx_util.To_Xsd_Date_String(P_Gt_Inv_Date_From) AS "GTDateFrom"
, jmf_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"
, jmf_gta_trx_util.To_Xsd_Date_String(P_Ar_Inv_Date_From) AS "ARTrxDateFrom"
, jmf_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",jmf_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",jmf_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
/*JMF_GTA_TRX_UTIL.Get_Arinvoice_Amount(rct.customer_trx_id
,rct.invoice_currency_code
,rct.trx_date
,l_org_id) amount
, JMF_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
, rac_bill_party.party_name 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
,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
)
) 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
/*JMF_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
amount
, JMF_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.
FROM
jmf_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'
AND gta.latest_version_flag='Y'
AND gta.invoice_type = lk.lookup_code --added by subba for R12.1
AND lk.lookup_type='JMF_GTA_INVOICE_TYPE';
SELECT
JMF_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gt.gta_trx_header_id)
amount
, JMF_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
jmf_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='JMF_GTA_INVOICE_TYPE';
SELECT
JMF_GTA_TRX_UTIL.Get_Gtainvoice_Amount(gta.gta_trx_header_id)
amount
, JMF_GTA_TRX_UTIL.Get_Gtainvoice_Tax_Amount(gta.gta_trx_header_id)
taxamount
, gta.gta_trx_number
FROM
jmf_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='TAX_PAYER_ID';*/
INSERT INTO jmf_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 jmf_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 jmf_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 jmf_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 jmf_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
jmf_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
jmf_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 jmf_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
jmf_gta_trx_headers_all gt_header
WHERE gt_header.source='GT'
AND gt_header.gta_trx_number=l_gta_trx_number
);
INSERT INTO jmf_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 jmf_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
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
,jgtl.amount
,jgtl.tax_amount
,jgtl.tax_rate
,jgtl.quantity
,jgtl.unit_price
,jgtl.uom
,jgtl.matched_flag
FROM
jmf_gta_trx_headers jgth
,jmf_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='Y'
ORDER BY jgth.gta_trx_number;
SELECT
COUNT(*)
FROM
jmf_gta_trx_headers jgth
,jmf_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
jmf_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
jmf_gta_trx_headers jgth
,jmf_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
jmf_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
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
jmf_gta_trx_headers jgth
,jmf_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
flv.meaning
INTO
l_goods_description_attr
FROM
fnd_lookup_values_vl flv
WHERE flv.lookup_type='JMF_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_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='JMF_GTA_DISCREPANCY_ATTRIBUTE'
AND flv.lookup_code='TAX_REGISTRATION_NUMBER';
INSERT INTO jmf_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 jmf_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 jmf_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 jmf_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 jmf_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 jmf_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 jmf_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 jmf_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 jmf_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 jmf_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
jmf_gta_trx_headers gta_header
,jmf_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
jmf_gta_trx_headers gt_header
,jmf_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';
INSERT INTO jmf_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 jmf_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
jmf_gta_ar_difference_temp;
SELECT
COUNT(*)
FROM
jmf_gta_missing_artrx_temp;
SELECT
adt.customer_trx_id
,adt.trx_number
,adt.trx_date
,adt.customer_name
,adt.invoice_currency_code
FROM
jmf_gta_ar_difference_temp adt;
SELECT
COUNT(DISTINCT ar_line_id)
FROM
jmf_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
jmf_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
jmf_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
jmf_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
jmf_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
jmf_gta_missing_artrx_temp;
SELECT
gt_currency_code
FROM
jmf_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
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_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
jmf_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'
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));
SELECT
gth.gta_trx_number
,gth.ra_trx_number
,gth.gt_invoice_number
,gth.gta_trx_header_id
FROM
jmf_gta_trx_headers gth
WHERE gth.SOURCE='GT'
AND gth.ra_trx_id=l_ar_trx_header_id;
SELECT
DISTINCT gth.ra_trx_number
FROM
jmf_gta_trx_headers gth
WHERE gth.SOURCE='GT'
AND gth.ra_trx_id=l_ar_trx_header_id;
INSERT INTO jmf_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 jmf_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
);