[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(import_seq)
INTO x_line_seq
FROM AR_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 AR_GTA_TRXIMP_TMP
WHERE import_seq=x_line_seq;
DELETE AR_GTA_TRXIMP_TMP;
SELECT GTA_TRX_HEADER_ID
, org_id
, status
INTO l_header_id
, l_org_id
, l_status
FROM AR_GTA_TRX_HEADERS_ALL
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='AR'
AND latest_version_flag='Y';
SELECT decode(l_values(1),'0','COMPLETED','CANCELLED') INTO l_new_status FROM dual;
DELETE AR_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='GT';
DELETE AR_GTA_TRX_LINES
WHERE GTA_TRX_HEADER_ID IN
( SELECT GTA_TRX_HEADER_ID
FROM AR_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER=l_GTA_Invoice_num
AND SOURCE='GT');
UPDATE AR_GTA_TRX_LINES
SET matched_flag='N'
WHERE GTA_TRX_HEADER_ID=l_header_id;
DELETE AR_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER = l_GTA_Invoice_num
AND SOURCE = 'GT';
DELETE AR_GTA_TRX_LINES
WHERE GTA_TRX_HEADER_ID IN (SELECT GTA_TRX_HEADER_ID
FROM AR_GTA_TRX_HEADERS
WHERE GTA_TRX_NUMBER = l_GTA_Invoice_num
AND SOURCE = 'GT');
UPDATE AR_GTA_TRX_LINES
SET matched_flag='N'
WHERE GTA_TRX_HEADER_ID=l_header_id;
/*UPDATE AR_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 AR_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 ar_gta_trx_headers_all_s.NEXTVAL
INTO l_trx_header_rec.gta_trx_header_id
FROM dual;
INSERT INTO AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES(AR_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 ar_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 round(unit_price,2)=round(decode(l_values(10),
'0',l_values(9),
'1',to_number(l_values(9))/
(1+to_number(l_values(7))),
NULL),2)--Qiong changed for bug 10638369
AND round(amount,2)=round(l_values(6),2)--Qiong changed for bug 10638369
AND matched_flag='N'
AND tax_amount=l_values(8)
AND ROWNUM<2;
SELECT ar_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, STATUS
)
VALUES( AR_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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
);
DELETE FROM AR_GTA_IMPORT_REP_TEMP
WHERE Invoice_Num = l_vat_tbl(l_GTA_Invoice_num).gt_invoice_number;
INSERT INTO AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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 AR_GTA_IMPORT_REP_TEMP( SEQ
, SUCCEEDED
, Customer_Name
, Taxpayer_ID
, Invoice_Num
, Invoice_date
, Amount
, FailedReason
)
VALUES( AR_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
);
UPDATE AR_GTA_TRX_HEADERS
SET Status = l_vat_tbl(l_vat_tbl_index).status,
gt_invoice_date = l_vat_tbl(l_vat_tbl_index).gt_invoice_date,
gt_invoice_net_amount = l_vat_tbl(l_vat_tbl_index).gt_invoice_net_amount,
gt_invoice_tax_amount = l_vat_tbl(l_vat_tbl_index).gt_invoice_tax_amount,
gt_tax_month = l_vat_tbl(l_vat_tbl_index).gt_tax_month,
gt_invoice_number = l_vat_tbl(l_vat_tbl_index).gt_invoice_number,
gt_invoice_type = l_vat_tbl(l_vat_tbl_index).gt_invoice_type,
gt_invoice_class = l_vat_tbl(l_vat_tbl_index).gt_invoice_class
WHERE GTA_TRX_HEADER_ID = l_vat_tbl(l_vat_tbl_index).gta_trx_header_id;
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
--,AR_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"--deleted by Yao for bug#8257757
,Invoice_date AS "InvoiceDate"--Added by Yao Zhang for bug#8257757
--Jogen 20-Sep-2006 bug5521629
,Amount AS "Amount"
,Status AS "Status"
)
)
)
)
INTO l_succ_XML
FROM AR_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
--,AR_GTA_TRX_UTIL.To_Xsd_Date_String(Invoice_date)AS "InvoiceDate"--delete by Yao for bug#8257757
,Invoice_date AS "InvoiceDate"--Added by Yao for bug#8257757
--Jogen 20-Sep-2006 bug5521629
,Amount AS "Amount"
,FailedReason AS "Reason"
)
)
)
)
INTO l_failed_XML
FROM AR_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 AR_GTA_trx_lines
WHERE GTA_trx_header_id = p_header_id
AND Enabled_Flag='Y';
SELECT uom.unit_of_measure_tl
INTO l_uom_name
FROM mtl_units_of_measure_tl uom
WHERE uom.uom_code = l_invoice_line.uom
AND uom.LANGUAGE = userenv('LANG');
UPDATE ar_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 ar_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 ar_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 AR_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;
SELECT s.auto_batch_numbering_flag
INTO l_batch_numbering_flag
FROM ar_gta_system_parameters_all s
WHERE s.org_id=P_ORG_ID;
SELECT COUNT(*)
INTO l_rows_same_batch
FROM ar_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 AR_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 AR_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 AR_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 AR_GTA_TRX_HEADERS JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT h.description
FROM AR_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 AR_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 AR_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 AR_GTA_TRX_HEADERS JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT h.description
FROM AR_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 AR_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 AR_GTA_TRX_HEADERS
WHERE Gta_Batch_Number=P_Batch_ID
AND status='GENERATED';
SELECT *
FROM AR_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 AR_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 AR_GTA_TRX_HEADERS_ALL JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT description
FROM AR_GTA_TRX_HEADERS_ALL
WHERE Generator_Id=p_generator_ID
AND status='DRAFT') )*/
AND JGTHA.description IN ( SELECT description
FROM AR_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 AR_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 AR_GTA_TRX_HEADERS_ALL JGTH
WHERE JGTH.status IN ('GENERATED', 'COMPLETED')
INTERSECT
SELECT description
FROM AR_GTA_TRX_HEADERS_ALL
WHERE Generator_Id=p_generator_ID
AND status='DRAFT') ) ;*/
AND JGTHA.description IN ( SELECT description
FROM AR_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
, AR_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
-- modified by Allen Yang 15/Jun/2009 for bug 8605196
-- to support export customer name in Chinese
------------------------------------------------------
, decode(CUST_PARTY.Known_As
, null
, CUST_PARTY.PARTY_NAME
, CUST_PARTY.Known_As)
--, 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--yao zhang delete for bug 8230998
AND cust_party.party_name BETWEEN p_name_from
AND decode(p_name_to,null,cust_party.party_name,p_name_to)--yao zhang add for bug 8230998
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 *
FROM
(SELECT
ibybanks.bank_account_name
, ibybanks.bank_account_num
, decode(bp.organization_name_phonetic
,null
,bp.party_name
,bp.organization_name_phonetic) bank_name
, decode(br.organization_name_phonetic
,null
,br.party_name
,br.organization_name_phonetic) bank_branch_name
FROM
IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
, HZ_PARTIES BR
, HZ_PARTIES BP
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = p_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND ibybanks.currency_code = p_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'))
AND ibybanks.bank_id = bp.party_id(+)
AND ibybanks.branch_id = br.party_id(+)
ORDER BY ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
UNION ALL
SELECT *
FROM
(SELECT
ibybanks.bank_account_name
, ibybanks.bank_account_num
, decode(bp.organization_name_phonetic
,null
,bp.party_name
,bp.organization_name_phonetic) bank_name
, decode(br.organization_name_phonetic
,null
,br.party_name
,br.organization_name_phonetic) bank_branch_name
FROM
IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
, HZ_PARTIES BR
, HZ_PARTIES BP
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = p_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND ibybanks.currency_code IS NULL
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'))
AND ibybanks.bank_id = bp.party_id(+)
AND ibybanks.branch_id = br.party_id(+)
ORDER BY ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE);
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--yao zhang delete for bug 8230998
AND cust_party.party_name BETWEEN l_customer_name_from
AND decode(l_customer_name_to,null,cust_party.party_name,l_customer_name_to)--yao zhang add for bug 8230998
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
-- Mofidied by Allen Yang 15/Jun/2009 for bug 8605196 to export customer address in Chinese
-------------------------------------------------------------------------------------------
decode(loc.Address_Lines_Phonetic
, null
, 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)
, loc.Address_Lines_Phonetic)
/*
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
AR_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
, bank_name --add by Yao Zhang for bug#7670710
, bank_branch_name --add by Yao Zhang for bug#7670710
INTO
l_bank_account_name
, l_bank_account_num
,l_bank_name--add by Yao Zhang for bug#7670710
,l_bank_branch_name --add by Yao Zhang for bug#7670710
FROM (SELECT ibybanks.bank_account_name
, ibybanks.bank_account_num
, bp.party_name bank_name --add by Yao Zhang for bug#7670710
, br.party_name bank_branch_name --add by Yao Zhang for bug#7670710
FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
,HZ_PARTIES BR
,HZ_PARTIES BP
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'))
AND ibybanks.bank_id = bp.party_id(+)--add by Yao Zhang for bug#7670710
AND ibybanks.branch_id = br.party_id(+) --add by Yao Zhang for bug#7670710
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 Yao Zhang comment for bug 8339490
,muom.unit_of_measure_tl uom
FROM
mtl_system_items_b_kfv items
,mtl_item_categories mic
,mtl_category_sets_b mcs
,mtl_categories_b_kfv mc
,mtl_units_of_measure_tl muom --yao zhang add for bug 8339490
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))
--Yao Zhang add for bug 8339490
AND muom.uom_code = items.primary_uom_code
AND muom.LANGUAGE = userenv('LANG');
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
ar_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
--Yao Zhang fix bug 7812065 add
,'ATTRIBUTE16'
,ATTRIBUTE16
,'ATTRIBUTE17'
,ATTRIBUTE17
,'ATTRIBUTE18'
,ATTRIBUTE18
,'ATTRIBUTE19'
,ATTRIBUTE19
,'ATTRIBUTE20'
,ATTRIBUTE20
,'ATTRIBUTE21'
,ATTRIBUTE21
,'ATTRIBUTE22'
,ATTRIBUTE22
,'ATTRIBUTE23'
,ATTRIBUTE23
,'ATTRIBUTE24'
,ATTRIBUTE24
,'ATTRIBUTE25'
,ATTRIBUTE25
,'ATTRIBUTE26'
,ATTRIBUTE26
,'ATTRIBUTE27'
,ATTRIBUTE27
,'ATTRIBUTE28'
,ATTRIBUTE28
,'ATTRIBUTE29'
,ATTRIBUTE29
,'ATTRIBUTE30'
,ATTRIBUTE30
--Yao Zhang add end
,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
--Yao Zhang fix bug 7812065 add
,'ATTRIBUTE16'
,ATTRIBUTE16
,'ATTRIBUTE17'
,ATTRIBUTE17
,'ATTRIBUTE18'
,ATTRIBUTE18
,'ATTRIBUTE19'
,ATTRIBUTE19
,'ATTRIBUTE20'
,ATTRIBUTE20
,'ATTRIBUTE21'
,ATTRIBUTE21
,'ATTRIBUTE22'
,ATTRIBUTE22
,'ATTRIBUTE23'
,ATTRIBUTE23
,'ATTRIBUTE24'
,ATTRIBUTE24
,'ATTRIBUTE25'
,ATTRIBUTE25
,'ATTRIBUTE26'
,ATTRIBUTE26
,'ATTRIBUTE27'
,ATTRIBUTE27
,'ATTRIBUTE28'
,ATTRIBUTE28
,'ATTRIBUTE29'
,ATTRIBUTE29
,'ATTRIBUTE30'
,ATTRIBUTE30
--Yao Zhang add end
,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;