[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(import_seq)
INTO x_line_seq
FROM JMF_GTA_TRXIMP_TMP
WHERE import_seq>=x_line_seq;
x_values.DELETE;
SELECT col1
, col2
, col3
, col4
, col5
, col6
, col7
, col8
, col9
, col10
, col11
, col12
, col13
, col14
, col15
, col16
, col17
, col18
, col19
, col20
, col21
, col22
, col23
, col24
, col25
INTO x_values(1)
, x_values(2)
, x_values(3)
, x_values(4)
, x_values(5)
, x_values(6)
, x_values(7)
, x_values(8)
, x_values(9)
, x_values(10)
, x_values(11)
, x_values(12)
, x_values(13)
, x_values(14)
, x_values(15)
, x_values(16)
, x_values(17)
, x_values(18)
, x_values(19)
, x_values(20)
, x_values(21)
, x_values(22)
, x_values(23)
, x_values(24)
, x_values(25)
FROM JMF_GTA_TRXIMP_TMP
WHERE import_seq=x_line_seq;
DELETE JMF_GTA_TRXIMP_TMP;
SELECT GTA_TRX_HEADER_ID
, org_id
, status
INTO l_header_id
, l_org_id
, l_status
FROM JMF_GTA_TRX_HEADERS_ALL
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='AR'
AND latest_version_flag='Y';
DELETE JMF_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='GT';
DELETE JMF_GTA_TRX_LINES
WHERE GTA_TRX_HEADER_ID IN
( SELECT GTA_TRX_HEADER_ID
FROM JMF_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='GT');
UPDATE JMF_GTA_TRX_LINES
SET matched_flag='N'
WHERE GTA_TRX_HEADER_ID=l_header_id;
UPDATE JMF_GTA_TRX_HEADERS
SET Status=l_new_status
, gt_invoice_date = l_Invoice_date
, gt_invoice_net_amount = l_values(10)
, gt_invoice_tax_amount = l_values(12)
, gt_tax_month = l_values(8)
, gt_invoice_number = l_values(5)
, gt_invoice_type = l_values(3)
, gt_invoice_class = l_values(4)
WHERE GTA_TRX_HEADER_ID=l_header_id;
SELECT ra_gl_date
, ra_gl_period
, set_of_books_id
, bill_to_customer_id
, bill_to_customer_number
, org_id
, rule_header_id
, gta_trx_number
, group_number
, version
, transaction_date
, ra_trx_id
, ra_currency_code
, conversion_type
, conversion_date
, conversion_rate
, gta_batch_number
, generator_id
, ra_trx_number
, Fp_Tax_Registration_Number
, Tp_Tax_Registration_Number
, Legal_Entity_Id
INTO
l_trx_header_rec.ra_gl_date
, l_trx_header_rec.ra_gl_period
, l_trx_header_rec.set_of_books_id
, l_trx_header_rec.bill_to_customer_id
, l_trx_header_rec.bill_to_customer_number
, l_trx_header_rec.org_id
, l_trx_header_rec.rule_header_id
, l_trx_header_rec.gta_trx_number
, l_trx_header_rec.group_number
, l_trx_header_rec.version
, l_trx_header_rec.transaction_date
, l_trx_header_rec.ra_trx_id
, l_trx_header_rec.ra_currency_code
, l_trx_header_rec.conversion_type
, l_trx_header_rec.conversion_date
, l_trx_header_rec.conversion_rate
, l_trx_header_rec.gta_batch_number
, l_trx_header_rec.generator_id
, l_trx_header_rec.ra_trx_number
, l_trx_header_rec.Fp_Tax_Registration_Number
, l_trx_header_rec.Tp_Tax_Registration_Number
, l_trx_header_rec.Legal_Entity_Id
FROM JMF_GTA_TRX_HEADERS
WHERE GTA_TRX_HEADER_ID=l_header_id;
l_trx_header_rec.program_update_date:= SYSDATE;
l_trx_header_rec.last_update_date := SYSDATE;
l_trx_header_rec.last_updated_by := fnd_global.USER_ID;
l_trx_header_rec.last_update_login := fnd_global.LOGIN_ID;
SELECT jmf_gta_trx_headers_all_s.NEXTVAL
INTO l_trx_header_rec.gta_trx_header_id
FROM dual;
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES(JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
l_trx_line_tbl.DELETE;
UPDATE jmf_gta_trx_lines_all
SET matched_flag='Y'
WHERE gta_trx_header_id=l_header_id
AND enabled_flag='Y'
AND item_description=l_values(2)
AND item_model=l_values(3)
AND item_tax_denomination=l_values(11)
AND tax_rate=l_values(7)
AND uom_name=l_values(4)
AND quantity=l_values(5)
AND unit_price=decode(l_values(10),
'0',l_values(9),
'1',to_number(l_values(9))/
(1+to_number(l_values(7))),
NULL)
AND amount=l_values(6)
AND matched_flag='N'
AND tax_amount=l_values(8)
AND ROWNUM<2;
SELECT jmf_gta_trx_lines_all_s.NEXTVAL
INTO l_trx_line_rec.gta_trx_line_id
FROM dual;
SELECT decode(l_values(10),
'0',l_values(9),
NULL)
, decode(l_values(10),
'1',l_values(9),
NULL)
INTO l_trx_line_rec.unit_price
, l_trx_line_rec.unit_tax_price
FROM dual;
l_trx_line_rec.last_update_date := SYSDATE;
l_trx_line_rec.last_updated_by := fnd_global.USER_ID;
l_trx_line_rec.last_update_login:= fnd_global.LOGIN_ID;
l_trx_line_rec.program_update_date := SYSDATE;
, 'Update original record:'||SQLCODE||':'||SQLERRM);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, STATUS
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'Y'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_new_status
);
, 'Insert into base table:'||SQLCODE||':'||SQLERRM);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_TP_TAX_REG_NUMBER
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
INSERT INTO JMF_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( JMF_GTA_IMPORT_REP_TEMP_s.NEXTVAL
, 'N'
, l_Customer_Name
, l_Taxpayer_ID
, l_Invoice_Num
, to_char(l_Invoice_date,l_date_format)
, l_Amount
, l_error_msg
);
SELECT XMLElement("Details"
, xmlagg(
xmlelement(
"Invoice"
, xmlforest(Customer_Name AS "CustomerName"
,Taxpayer_ID AS "TaxpayerID"
,Invoice_Num AS "InvoiceNum"
--Jogen 20-Sep-2006 bug5521629
--Format date to XSD Date format
,JMF_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"
--,Invoice_date AS "InvoiceDate"
--Jogen 20-Sep-2006 bug5521629
,Amount AS "Amount"
,Status AS "Status"
)
)
)
)
INTO l_succ_XML
FROM JMF_GTA_IMPORT_REP_TEMP
WHERE SUCCEEDED='Y';
SELECT XMLElement("FailedInvoices"
, xmlagg(
xmlelement(
"Invoice"
,xmlforest(Customer_Name AS "CustomerName"
,Taxpayer_ID AS "TaxpayerID"
,Invoice_Num AS "InvoiceNum"
--Jogen 20-Sep-2006 bug5521629
--Format date to XSD Date format
,JMF_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"
--,Invoice_date AS "InvoiceDate"
--Jogen 20-Sep-2006 bug5521629
,Amount AS "Amount"
,FailedReason AS "Reason"
)
)
)
)
INTO l_failed_XML
FROM JMF_GTA_IMPORT_REP_TEMP
WHERE SUCCEEDED='N';
SELECT XMLElement("ImportReport"
, XMLElement("RepDate",to_char( SYSDATE
, l_date_format
)
)
, XMLElement("ReportFailed",'N')
, XMLElement("FailedWithParameters",'N')
, l_succ_XML
, l_failed_XML
)
INTO l_report_XML
FROM dual;
SELECT *
FROM JMF_GTA_trx_lines
WHERE GTA_trx_header_id = p_header_id
AND Enabled_Flag='Y';
UPDATE jmf_gta_trx_headers
SET status='GENERATED'
, gta_batch_number =p_batch_number
, export_request_id =fnd_global.CONC_REQUEST_ID
, REQUEST_ID =fnd_global.CONC_REQUEST_ID
, PROGRAM_APPLICATION_ID=fnd_global.RESP_APPL_ID
, PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
, PROGRAM_UPDATE_DATE =SYSDATE
, LAST_UPDATE_DATE =SYSDATE
, LAST_UPDATED_BY =fnd_global.USER_ID
, LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id;
UPDATE jmf_gta_trx_headers
SET export_request_id =fnd_global.CONC_REQUEST_ID
, REQUEST_ID =fnd_global.CONC_REQUEST_ID
, PROGRAM_APPLICATION_ID=fnd_global.RESP_APPL_ID
, PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
, PROGRAM_UPDATE_DATE =SYSDATE
, LAST_UPDATE_DATE =SYSDATE
, LAST_UPDATED_BY =fnd_global.USER_ID
, LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id;
UPDATE jmf_gta_trx_lines
SET REQUEST_ID =fnd_global.CONC_REQUEST_ID
, program_application_id =fnd_global.RESP_APPL_ID
, PROGRAM_ID =fnd_global.CONC_PROGRAM_ID
, PROGRAM_UPDATE_DATE =SYSDATE
, LAST_UPDATE_DATE =SYSDATE
, LAST_UPDATED_BY =fnd_global.USER_ID
, LAST_UPDATE_LOGIN =fnd_global.LOGIN_ID
WHERE GTA_TRX_HEADER_ID=l_invoice.gta_Trx_header_id
AND ENABLED_FLAG='Y';
SELECT DISTINCT RCTT.type
INTO lv_trx_type
FROM JMF_GTA_TRX_HEADERS_ALL JGTH
, RA_CUST_TRX_TYPES_ALL RCTT
, RA_CUSTOMER_TRX_ALL RCT
WHERE RCTT.cust_trx_type_id = RCT.cust_trx_type_id
AND JGTH.source = 'AR'
AND RCTT.org_id = p_GTA_trx_header.org_id
AND RCT.customer_trx_id = p_GTA_trx_header.ra_trx_id;
SELECT to_number(lv_trx_crmemo_notification_num)
INTO ln_notification_num
FROM dual;
SELECT hr.name
INTO ln_dup_org_name1
FROM hr_operating_units hr
WHERE hr.organization_id = p_GTA_trx_header.org_id;
SELECT hr.name
INTO ln_dup_org_name2
FROM hr_operating_units hr
WHERE hr.organization_id = ln_trx_org_id;
put_line(l_error_msg||'..pls check the invoice type you selected');
SELECT s.auto_batch_numbering_flag
INTO l_batch_numbering_flag
FROM jmf_gta_system_parameters_all s
WHERE s.org_id=P_ORG_ID;
SELECT COUNT(*)
INTO l_rows_same_batch
FROM jmf_gta_trx_headers
WHERE gta_batch_number=x_batch_number;
IF P_Invoice_Type_ID <>'A' THEN --if user selects a particular invoice_type
OPEN l_cur_header FOR
SELECT
h.*
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id(+)
AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND ar.trx_number BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
AND trunc(ar.trx_date,'DDD') BETWEEN l_AR_Trx_Date_From --jogen Mar-22, 2006
AND l_AR_Trx_Date_To -- bug 5107043
AND trunc(gd.GL_DATE,'DDD') BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To --jogen Mar-22, 2006
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'
AND h.invoice_type = P_Invoice_Type_ID; --added by subba.
SELECT JGTHA.ra_trx_id
, JGTHA.description
, JGTHA.org_id
, JGTHA.gta_trx_number
FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
AND JGTHA.status IN ('GENERATED', 'COMPLETED')
AND JGTHA.description IN /*( SELECT h.description
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE ( h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id
AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND h.ra_trx_number BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
AND trunc(h.transaction_date,'DDD') BETWEEN l_AR_Trx_Date_From
AND l_AR_Trx_Date_To
AND trunc(h.ra_gl_date,'DDD') BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'
AND h.invoice_type = P_Invoice_Type_ID)
OR ( h.status IN ('GENERATED', 'COMPLETED'))
GROUP BY h.description
HAVING COUNT(h.description) > 1)*/
( SELECT DISTINCT description
FROM (SELECT description
FROM JMF_GTA_TRX_HEADERS JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT h.description
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id(+)
--AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
--AND ar.trx_number
AND h.ra_trx_number
BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
--AND trunc(ar.trx_date,'DDD')
--AND trunc(h.transaction_date,'DDD')
AND h.transaction_date
BETWEEN l_AR_Trx_Date_From
AND l_AR_Trx_Date_To
--AND trunc(gd.GL_DATE,'DDD')
--AND trunc(h.RA_GL_DATE,'DDD')
AND h.RA_GL_DATE
BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'
AND h.invoice_type = P_Invoice_Type_ID))
UNION ALL
SELECT JGTHA.ra_trx_id
, JGTHA.description
, JGTHA.org_id
, JGTHA.gta_trx_number
FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
AND JGTHA.status = 'DRAFT'
AND JGTHA.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND JGTHA.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = JGTHA.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id(+)
--AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
AND JGTHA.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,JGTHA.BILL_TO_CUSTOMER_ID)
AND JGTHA.rule_header_id = nvl(p_transfer_rule_id,JGTHA.rule_header_id)
AND JGTHA.fp_tax_registration_number = p_FP_Tax_reg_Number
--AND ar.trx_number
AND JGTHA.ra_trx_number
BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
--AND trunc(ar.trx_date,'DDD')
--AND trunc(JGTHA.transaction_date,'DDD')
AND JGTHA.transaction_date
BETWEEN l_AR_Trx_Date_From
AND l_AR_Trx_Date_To
--AND trunc(gd.GL_DATE,'DDD')
--AND trunc(JGTHA.RA_GL_DATE,'DDD')
AND JGTHA.RA_GL_DATE
BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND JGTHA.latest_version_flag = 'Y'
AND JGTHA.SOURCE = 'AR'
AND JGTHA.invoice_type = P_Invoice_Type_ID
AND JGTHA.description IN /*( SELECT h.description
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE ( h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id
AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND h.ra_trx_number BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
AND trunc(h.transaction_date,'DDD') BETWEEN l_AR_Trx_Date_From
AND l_AR_Trx_Date_To
AND trunc(h.ra_gl_date,'DDD') BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'
AND h.invoice_type = P_Invoice_Type_ID)
OR ( h.status IN ('GENERATED', 'COMPLETED') )
GROUP BY h.description
HAVING COUNT(h.description) > 1)*/
( SELECT DISTINCT description
FROM (SELECT description
FROM JMF_GTA_TRX_HEADERS JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT h.description
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id(+)
--AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
AND h.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,h.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
--AND ar.trx_number
AND h.ra_trx_number
BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
--AND trunc(ar.trx_date,'DDD')
--AND trunc(h.transaction_date,'DDD')
AND h.transaction_date
BETWEEN l_AR_Trx_Date_From
AND l_AR_Trx_Date_To
--AND trunc(gd.GL_DATE,'DDD')
--AND trunc(h.RA_GL_DATE,'DDD')
AND h.RA_GL_DATE
BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'
AND h.invoice_type = P_Invoice_Type_ID));
ELSE --user selects 'All Invoices'
NULL;
* can't be selected as 'ALL' in this change.
*/
/* OPEN l_cur_header FOR
SELECT
h.*
FROM JMF_GTA_TRX_HEADERS h
, ra_customer_trx_all ar
, Ra_Cust_Trx_Types_all ctt
, RA_CUST_TRX_LINE_GL_DIST_all gd
, ra_batches_all b
WHERE h.org_id = p_ORG_ID
AND ar.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID
AND ctt.ORG_ID = p_org_id
AND h.fp_tax_registration_number = p_FP_Tax_reg_Number
AND h.RA_TRX_ID = ar.CUSTOMER_TRX_ID
AND GD.CUSTOMER_TRX_ID = h.RA_TRX_ID
AND GD.ACCOUNT_CLASS = 'REC'
AND GD.LATEST_REC_FLAG = 'Y'
AND gd.Org_Id = p_org_id
AND ar.BATCH_ID = b.batch_id(+)
AND ar.BILL_TO_CUSTOMER_ID=nvl(l_customer_id,ar.BILL_TO_CUSTOMER_ID)
AND h.rule_header_id = nvl(p_transfer_rule_id,h.rule_header_id)
AND ar.trx_number BETWEEN l_AR_Trx_Num_From
AND l_AR_Trx_Num_To
AND trunc(ar.trx_date,'DDD') BETWEEN l_AR_Trx_Date_From --jogen Mar-22, 2006
AND l_AR_Trx_Date_To -- bug 5107043
AND trunc(gd.GL_DATE,'DDD') BETWEEN l_AR_Trx_GL_Date_From
AND l_AR_Trx_GL_Date_To --jogen Mar-22, 2006
AND nvl(b.name,' ') BETWEEN l_AR_Trx_Batch_From
AND l_AR_Trx_Batch_To
AND ctt.TYPE = nvl(p_Trx_Class,ctt.type)
AND h.latest_version_flag = 'Y'
AND h.SOURCE = 'AR'
AND h.status = 'DRAFT'; */
SELECT *
FROM JMF_GTA_TRX_HEADERS
WHERE Gta_Batch_Number=P_Batch_ID
AND status='GENERATED';
SELECT *
FROM JMF_GTA_TRX_HEADERS
WHERE Generator_Id=p_generator_ID
AND status='DRAFT';
SELECT JGTHA.ra_trx_id
, JGTHA.description
, JGTHA.org_id
, JGTHA.gta_trx_number
FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
AND JGTHA.Invoice_Type<>'2'--yao zhang add for bug 7673309
AND JGTHA.source='AR'--yao zhang add for bug7673309
AND JGTHA.status IN ('GENERATED', 'COMPLETED')
AND JGTHA.org_id = p_org_id
--modified by Lv Xiao for bug#7644803 on 16-Dec-08, begin
-------------------------------------------------------------------
/* AND JGTHA.description IN ( SELECT DISTINCT description
FROM (SELECT description
FROM JMF_GTA_TRX_HEADERS_ALL JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT description
FROM JMF_GTA_TRX_HEADERS_ALL
WHERE Generator_Id=p_generator_ID
AND status='DRAFT') )*/
AND JGTHA.description IN ( SELECT description
FROM JMF_GTA_TRX_HEADERS JGTH
WHERE (( status = 'DRAFT'
AND generator_id = p_generator_ID )
OR status IN ('GENERATED', 'COMPLETED'))
AND Invoice_Type<>'2'--yao zhang add for bug 7673309
AND source='AR'--yao zhang add for bug 7673309
GROUP BY description
HAVING COUNT(description) > 1)
UNION ALL
SELECT JGTHA.ra_trx_id
, JGTHA.description
, JGTHA.org_id
, JGTHA.gta_trx_number
FROM JMF_GTA_TRX_HEADERS_ALL JGTHA
WHERE Get_Trx_Class(JGTHA.org_id, JGTHA.ra_trx_id) = 'CM'
AND JGTHA.Invoice_Type<>'2'--Yao Zhang add for bug 7673309
AND JGTHA.source='AR'--Yao Zhang add for bug 7673309
AND JGTHA.status = 'DRAFT'
AND JGTHA.generator_id = p_generator_ID
AND JGTHA.org_id = p_org_id
/* AND JGTHA.description IN ( SELECT DISTINCT description
FROM (SELECT description
FROM JMF_GTA_TRX_HEADERS_ALL JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT description
FROM JMF_GTA_TRX_HEADERS_ALL
WHERE Generator_Id=p_generator_ID
AND status='DRAFT') ) ;*/
AND JGTHA.description IN ( SELECT description
FROM JMF_GTA_TRX_HEADERS JGTH
WHERE (( status = 'DRAFT'
AND generator_id = p_generator_ID )
OR status IN ('GENERATED', 'COMPLETED'))
AND Invoice_Type<>'2'--Yao Zhang add for bug7673309
AND source='AR'--Yao Zhang add for bug7673309
GROUP BY description
HAVING COUNT(description) > 1);
SELECT DISTINCT RCTT.type
INTO lv_class_type
FROM RA_CUST_TRX_TYPES_ALL RCTT
, RA_CUSTOMER_TRX_ALL RCT
, JMF_GTA_TRX_HEADERS_ALL JGTH
WHERE RCTT.cust_trx_type_id = RCT.cust_trx_type_id
AND JGTH.source = 'AR'
AND RCTT.org_id = p_GTA_org_id
AND RCT.customer_trx_id = p_GTA_trx_id
AND JGTH.ra_trx_id = p_GTA_trx_id;
SELECT t.TYPE
INTO l_trx_class
FROM ra_customer_trx_all ct
, ra_cust_trx_types_all t
WHERE ct.cust_trx_type_id = t.cust_trx_type_id
AND ct.customer_trx_id=p_GTA_trx_header.ra_trx_id
AND t.org_id=p_GTA_trx_header.org_id;
SELECT
CUST.CUST_ACCOUNT_ID
, CUST.ACCOUNT_NUMBER
, CUST_PARTY.PARTY_NAME
--, CUST_PARTY.JGZZ_FISCAL_CODE
, CUST_PARTY.ORGANIZATION_NAME_PHONETIC
, CUST_PARTY.PARTY_ID
FROM
HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES CUST_PARTY
WHERE cust.party_id = cust_party.party_id
AND cust.account_number BETWEEN p_num_from AND p_num_to
AND cust_party.party_name BETWEEN p_name_from AND p_name_to
AND cust.creation_date BETWEEN p_create_from AND p_create_to
--AND (cust_party.jgzz_fiscal_code = p_taxpayer_id OR p_taxpayer_id IS NULL)
AND cust.status = 'A'
AND cust_party.party_type = 'ORGANIZATION';
SELECT
COUNT(cust.cust_account_id)
INTO
l_count
FROM
HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES CUST_PARTY
WHERE cust.party_id = cust_party.party_id
AND cust.account_number BETWEEN l_customer_num_from AND l_customer_num_to
AND cust_party.party_name BETWEEN l_customer_name_from AND l_customer_name_to
AND cust.creation_date BETWEEN l_creation_date_from AND l_creation_date_to
--AND (cust_party.jgzz_fiscal_code = l_taxpayer_id OR l_taxpayer_id IS NULL)
AND cust.status = 'A'
AND cust_party.party_type = 'ORGANIZATION';
SELECT
arp_addr_pkg.format_address(loc.address_style
, loc.address1
, loc.address2
, loc.address3
, loc.address4
, loc.city
, loc.county
, loc.state
, loc.province
, loc.postal_code
, terr.territory_short_name )
, addr.CUST_ACCT_SITE_ID
, party_site.party_site_id
INTO
l_address
, l_customer_site_id
, l_party_site_id
FROM
hz_cust_site_uses_all hcsua
, hz_cust_acct_sites_all addr
, hz_party_sites party_site
, hz_locations loc
, fnd_territories_tl terr
WHERE addr.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND hcsua.cust_acct_site_id = addr.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO'
AND hcsua.status = 'A'
AND hcsua.primary_flag = 'Y'
AND loc.country = terr.territory_code(+)
AND terr.LANGUAGE = USERENV('LANG')
AND addr.org_id = p_org_id
AND addr.cust_account_id = l_customer_id;
SELECT
phone_number
INTO
l_phone_num
FROM
Hz_Contact_Points
WHERE owner_table_name='HZ_PARTY_SITES'
AND owner_table_id=l_party_site_id
AND phone_line_type='GEN'
AND primary_flag='Y'
AND status = 'A'
AND contact_point_type = 'PHONE';
SELECT
SITE.SITE_USE_ID
INTO
l_customer_site_use_id
FROM
hz_cust_site_uses site
WHERE SITE.CUST_ACCT_SITE_ID = l_customer_site_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.STATUS = 'A';
SELECT
GT_CURRENCY_CODE
INTO
l_currency_code
FROM
JMF_GTA_SYSTEM_PARAMETERS_ALL
WHERE
org_id = p_org_id;
SELECT
ext_payer_id
INTO
l_ext_payer_id
FROM
IBY_EXTERNAL_PAYERS_ALL
WHERE party_id = l_party_id
AND CUST_ACCOUNT_ID = l_customer_id -- site account id
AND ACCT_SITE_USE_ID = l_customer_site_use_id -- site use id
AND ORG_ID = p_org_id -- org id
AND org_type = 'OPERATING_UNIT' -- ou
AND payment_function = 'CUSTOMER_PAYMENT'; -- function
SELECT
bank_account_name
, bank_account_num
INTO
l_bank_account_name
, l_bank_account_num
FROM (SELECT ibybanks.bank_account_name
, ibybanks.bank_account_num
FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND ibybanks.currency_code = l_currency_code
AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
ORDER BY ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
WHERE ROWNUM =1;
SELECT
DISTINCT
items.inventory_item_id
,items.concatenated_segments item_number
,items.DESCRIPTION item_name
,items.primary_unit_of_measure uom
FROM
mtl_system_items_b_kfv items
,mtl_item_categories mic
,mtl_category_sets_b mcs
,mtl_categories_b_kfv mc
WHERE items.organization_id=l_master_org_id
AND items.inventory_item_status_code=nvl(l_item_status,items.inventory_item_status_code)
AND items.concatenated_segments>=nvl(l_item_num_from,items.concatenated_segments)
AND items.concatenated_segments<=nvl(l_item_num_to,items.concatenated_segments)
AND items.creation_date BETWEEN NVL(l_creation_date_from,items.creation_date)
AND NVL(l_creation_date_to,items.creation_date)
AND mic.organization_id(+)=l_master_org_id
AND mic.inventory_item_id(+)=items.inventory_item_id
AND ((mic.category_set_id=l_category_set_id) OR (l_category_set_id IS NULL))
AND mic.category_set_id=mcs.category_set_id(+)
AND mic.category_id=mc.category_id(+)
AND ((mcs.structure_id=l_structure_id) OR (l_structure_id IS NULL))
AND ((mc.concatenated_segments>=l_item_category_from) OR (l_item_category_from IS NULL))
AND ((mc.concatenated_segments<=l_item_category_to) OR (l_item_category_to IS NULL));
SELECT
reference1.cross_reference
FROM
mtl_cross_references reference1
WHERE reference1.inventory_item_id=l_inventory_item_id
AND (reference1.organization_id=l_master_org_id OR reference1.organization_id IS NULL)
AND reference1.cross_reference_type=l_cross_reference_type
AND reference1.creation_date=(SELECT
MAX(creation_date)
FROM
mtl_cross_references reference2
WHERE reference2.inventory_item_id=l_inventory_item_id
AND (reference2.organization_id=l_master_org_id OR reference2.organization_id IS NULL)
AND reference2.cross_reference_type=l_cross_reference_type
);
SELECT
inv_item_context_code
,inv_tax_attribute_column
,inv_model_attribute_column
FROM
jmf_gta_system_parameters
WHERE
org_id=l_org_id;
SELECT
decode(l_tax_name_column
,'ATTRIBUTE1'
,ATTRIBUTE1
,'ATTRIBUTE2'
,ATTRIBUTE2
,'ATTRIBUTE3'
,ATTRIBUTE3
,'ATTRIBUTE4'
,ATTRIBUTE4
,'ATTRIBUTE5'
,ATTRIBUTE5
,'ATTRIBUTE6'
,ATTRIBUTE6
,'ATTRIBUTE7'
,ATTRIBUTE7
,'ATTRIBUTE8'
,ATTRIBUTE8
,'ATTRIBUTE9'
,ATTRIBUTE9
,'ATTRIBUTE10'
,ATTRIBUTE10
,'ATTRIBUTE11'
,ATTRIBUTE11
,'ATTRIBUTE12'
,ATTRIBUTE12
,'ATTRIBUTE13'
,ATTRIBUTE13
,'ATTRIBUTE14'
,ATTRIBUTE14
,'ATTRIBUTE15'
,ATTRIBUTE15
,NULL
)
FROM
mtl_system_items_b
WHERE inventory_item_id=l_inventory_item_id
AND organization_id=l_master_org_id
AND attribute_category=l_item_attribute_category;
SELECT
decode(l_item_model_column
,'ATTRIBUTE1'
,ATTRIBUTE1
,'ATTRIBUTE2'
,ATTRIBUTE2
,'ATTRIBUTE3'
,ATTRIBUTE3
,'ATTRIBUTE4'
,ATTRIBUTE4
,'ATTRIBUTE5'
,ATTRIBUTE5
,'ATTRIBUTE6'
,ATTRIBUTE6
,'ATTRIBUTE7'
,ATTRIBUTE7
,'ATTRIBUTE8'
,ATTRIBUTE8
,'ATTRIBUTE9'
,ATTRIBUTE9
,'ATTRIBUTE10'
,ATTRIBUTE10
,'ATTRIBUTE11'
,ATTRIBUTE11
,'ATTRIBUTE12'
,ATTRIBUTE12
,'ATTRIBUTE13'
,ATTRIBUTE13
,'ATTRIBUTE14'
,ATTRIBUTE14
,'ATTRIBUTE15'
,ATTRIBUTE15
,NULL
)
FROM
mtl_system_items_b
WHERE inventory_item_id=l_inventory_item_id
AND organization_id=l_master_org_id
AND attribute_category=l_item_attribute_category;