[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT zxlgt.trx_line_id
FROM zx_transaction_lines_gt zxlgt
WHERE p_requisition_header_id=zxlgt.trx_id
AND p_requisition_header_id NOT IN (SELECT trx_id FROM zx_errors_gt zxegt)
AND zxlgt.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxlgt.entity_code = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxlgt.event_class_code = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE;
SELECT po_session_gt_s.NEXTVAL INTO l_po_session_gt_key FROM dual;
PO_LOG.stmt(d_module_base,d_progress,'initial values of tax_attribute_update_codes');
UPDATE po_line_locations_all pll
SET pll.tax_attribute_update_code =
NVL(
(SELECT NVL(pl.tax_attribute_update_code, ph.tax_attribute_update_code)
FROM po_headers_all ph, po_lines_all pl
WHERE pll.po_line_id = pl.po_line_id
AND pll.po_header_id = ph.po_header_id
-- following AND written only for clarity
--AND (pl.tax_attribute_update_code IS NOT NULL
-- or ph.tax_attribute_update_code IS NOT NULL)
)
,pll.tax_attribute_update_code
)
WHERE
pll.po_header_id = p_po_header_id_tbl(i)
AND (pll.tax_attribute_update_code IS NULL
OR pll.tax_attribute_update_code = 'DIST_DELETE');
UPDATE po_line_locations_all pll
SET pll.tax_attribute_update_code =
NVL(
(SELECT ph.tax_attribute_update_code
FROM po_headers_all ph
WHERE pll.po_header_id = ph.po_header_id
-- following AND written only for clarity
--AND ph.tax_attribute_update_code IS NOT NULL
)
,pll.tax_attribute_update_code
)
WHERE
pll.po_release_id = p_po_release_id_tbl(i)
AND (pll.tax_attribute_update_code IS NULL
OR pll.tax_attribute_update_code = 'DIST_DELETE');
PO_LOG.stmt(d_module_base,d_progress,'tax_attribute_update_codes after denormalization');
SELECT COUNT(1) INTO l_count FROM zx_transaction_lines_gt;
PO_LOG.stmt(d_module_base,d_progress,'Exception while selecting from zx_transaction_lines_gt');
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxvegt.trx_id
,zxvegt.trx_line_id
,null
,zxvegt.event_class_code
,zxvegt.message_text
,'VALIDATE'
,ph.segment1
FROM zx_validation_errors_gt zxvegt, po_headers_all ph
WHERE zxvegt.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxvegt.trx_id = ph.po_header_id;
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxvegt.trx_id
,zxvegt.trx_line_id
,null
,zxvegt.event_class_code
,zxvegt.message_text
,'VALIDATE'
,ph.segment1
FROM zx_validation_errors_gt zxvegt, po_headers_all ph,
po_releases_all pr
WHERE zxvegt.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zxvegt.trx_id = pr.po_release_id
AND pr.po_header_id = ph.po_header_id;
SELECT COUNT(1) INTO l_count FROM zx_validation_errors_gt;
DELETE FROM zx_trx_headers_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE);
DELETE FROM zx_transaction_lines_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE);
UPDATE zx_transaction_lines_gt zxlgt
SET zxlgt.product_type =
(SELECT DECODE(pl.purchase_basis,
'GOODS', 'GOODS',
'SERVICES')
FROM po_line_locations_all pll, po_lines_all pl
WHERE pll.line_location_id = zxlgt.trx_line_id
AND pll.po_line_id = pl.po_line_id)
WHERE zxlgt.product_type IS NULL
AND zxlgt.line_level_action = 'CREATE';
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxegt.trx_id
,zxegt.trx_line_id
,zxegt.trx_line_dist_id
,zxegt.event_class_code
,zxegt.message_text
,'CALCULATE_TAX'
,ph.segment1
FROM zx_errors_gt zxegt, po_headers_all ph
WHERE zxegt.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxegt.trx_id = ph.po_header_id;
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxegt.trx_id
,zxegt.trx_line_id
,zxegt.trx_line_dist_id
,zxegt.event_class_code
,zxegt.message_text
,'CALCULATE_TAX'
,ph.segment1
FROM zx_errors_gt zxegt, po_headers_all ph, po_releases_all pr
WHERE zxegt.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zxegt.trx_id = pr.po_release_id
AND pr.po_header_id = ph.po_header_id;
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
DELETE FROM zx_trx_headers_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND psgt.char3 = 'CALCULATE_TAX');
DELETE FROM zx_trx_headers_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND psgt.char3 = 'CALCULATE_TAX');
DELETE FROM zx_transaction_lines_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND psgt.char3 = 'CALCULATE_TAX');
DELETE FROM zx_transaction_lines_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND psgt.char3 = 'CALCULATE_TAX');
SELECT COUNT(1) INTO l_count FROM zx_itm_distributions_gt;
PO_LOG.stmt(d_module_base,d_progress,'Exception while selecting from zx_itm_distributions_gt');
UPDATE zx_trx_headers_gt zxhgt
SET zxhgt.event_type_code =
DECODE(zxhgt.event_class_code,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
NVL2((SELECT 'EXISTING DISTRIBUTIONS'
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM po_distributions_all pd
WHERE pd.po_header_id = zxhgt.trx_id
AND (pd.tax_attribute_update_code <> 'CREATE'
OR pd.tax_attribute_update_code IS NULL)
)
),
PO_CONSTANTS_SV.PO_REDISTRIBUTED,
PO_CONSTANTS_SV.PO_DISTRIBUTED
),
--Release
NVL2((SELECT 'EXISTING DISTRIBUTIONS'
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM po_distributions_all pd
WHERE pd.po_release_id = zxhgt.trx_id
AND (pd.tax_attribute_update_code <> 'CREATE'
OR pd.tax_attribute_update_code IS NULL)
)
),
PO_CONSTANTS_SV.REL_REDISTRIBUTED,
PO_CONSTANTS_SV.REL_DISTRIBUTED
)
);
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxegt.trx_id
,zxegt.trx_line_id
,zxegt.trx_line_dist_id
,zxegt.event_class_code
,zxegt.message_text
,'DETERMINE_RECOVERY'
,ph.segment1
FROM zx_errors_gt zxegt, po_headers_all ph
WHERE zxegt.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxegt.trx_id = ph.po_header_id;
INSERT INTO po_session_gt(
key
,index_num1
,num1
,num2
,char1
,char2
,char3
,char4
)
SELECT
l_po_session_gt_key
,zxegt.trx_id
,zxegt.trx_line_id
,zxegt.trx_line_dist_id
,zxegt.event_class_code
,zxegt.message_text
,'DETERMINE_RECOVERY'
,ph.segment1
FROM zx_errors_gt zxegt, po_headers_all ph, po_releases_all pr
WHERE zxegt.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zxegt.trx_id = pr.po_release_id
AND pr.po_header_id = ph.po_header_id;
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
DELETE FROM zx_itm_distributions_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND psgt.char3 = 'DETERMINE_RECOVERY');
DELETE FROM zx_itm_distributions_gt
WHERE trx_id IN (SELECT DISTINCT index_num1 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char1 = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND psgt.char3 = 'DETERMINE_RECOVERY');
UPDATE po_distributions_all
SET tax_attribute_update_code = 'NO_ACTION'
WHERE tax_attribute_update_code IS NULL
AND po_distribution_id IN (SELECT psgt.NUM2 FROM po_session_gt psgt
WHERE psgt.key = l_po_session_gt_key
AND psgt.char3 = 'DETERMINE_RECOVERY');
UPDATE po_distributions_all pd
SET pd.recoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'Y'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND ( (zxdist.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxdist.event_class_code =
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxdist.trx_id = pd.po_header_id)
OR
(zxdist.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zxdist.event_class_code =
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zxdist.trx_id = pd.po_release_id)))
,pd.nonrecoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'N'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND ( (zxdist.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxdist.event_class_code =
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxdist.trx_id = pd.po_header_id)
OR
(zxdist.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zxdist.event_class_code =
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zxdist.trx_id = pd.po_release_id)))
WHERE pd.po_distribution_id IN
(SELECT trx_line_dist_id FROM zx_itm_distributions_gt);
UPDATE po_headers_all ph
SET ph.tax_attribute_update_code = null
WHERE ph.po_header_id = p_po_header_id_tbl(i)
-- Bug 4774900 null out if even a single shipment has been processed
-- correctly. That would mean that the header has been recorded
-- in eTax, and next time we need to pass UPDATE
AND EXISTS (SELECT 'Y'
FROM zx_transaction_lines_gt zxlgt
WHERE zxlgt.trx_id=ph.po_header_id);
UPDATE po_releases_all pr
SET pr.tax_attribute_update_code = null
WHERE pr.po_release_id = p_po_release_id_tbl(i)
AND EXISTS (SELECT 'Y'
FROM zx_transaction_lines_gt zxlgt
WHERE zxlgt.trx_id=pr.po_release_id);
UPDATE po_lines_all
SET tax_attribute_update_code = null
WHERE po_header_id = p_po_header_id_tbl(i);
UPDATE po_line_locations_all
SET tax_attribute_update_code = null,
original_shipment_id = null
WHERE line_location_id IN (SELECT trx_line_id FROM zx_transaction_lines_gt);
UPDATE po_line_locations_all
SET tax_attribute_update_code = null,
original_shipment_id = null
WHERE tax_attribute_update_code = 'DIST_DELETE'
AND line_location_id IN (SELECT trx_line_id FROM zx_itm_distributions_gt);
UPDATE po_distributions_all
SET tax_attribute_update_code = null
WHERE po_distribution_id IN (SELECT trx_line_dist_id
FROM zx_itm_distributions_gt);
UPDATE po_line_locations_all pll
SET pll.taxable_flag =
DECODE((SELECT COUNT(1) FROM zx_lines zl
WHERE zl.trx_line_id = pll.line_location_id
AND zl.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND ( (zl.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_header_id)
OR
(zl.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_release_id))),
0, 'N',
'Y'
)
WHERE
pll.line_location_id IN (SELECT trx_line_id FROM zx_transaction_lines_gt);
SELECT
psgt.char3 --error_level
,decode(psgt.char1,--document_type_code
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE, PO_CONSTANTS_SV.PO,
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE, PO_CONSTANTS_SV.RELEASE
)
,psgt.index_num1 --document_id
,psgt.char4 --document_num
,null --line_id
,null --line_num
,pll.line_location_id --line_location_id
,pll.shipment_num --shipment_num
,pd.po_distribution_id --distribution_id
,pd.distribution_num --distribution_num
,psgt.char2 --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM po_session_gt psgt, po_line_locations_all pll, po_distributions_all pd
WHERE psgt.num1 = pll.line_location_id
AND psgt.num2 = pd.po_distribution_id(+)
AND psgt.key = l_po_session_gt_key;
PO_LOG.stmt(d_module_base,d_progress,'initial values of tax_attribute_update_codes');
SELECT COUNT(1) INTO l_line_count FROM zx_transaction_lines_gt;
PO_LOG.stmt(d_module_base,d_progress,'Exception while selecting from zx_transaction_lines_gt');
SELECT
'VALIDATE' --error_level,
,PO_CONSTANTS_SV.REQUISITION --document_type_code,
,p_requisition_header_id --document_id,
,prh.segment1 --document_num,
,zxvegt.trx_line_id --line_id,
,prl.line_num --line_num,
,null --line_location_id,
,null --shipment_num,
,null --distribution_id,
,null --distribution_num,
,zxvegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_validation_errors_gt zxvegt, po_requisition_headers_all prh,
po_requisition_lines_all prl
WHERE zxvegt.trx_id = prh.requisition_header_id
AND zxvegt.trx_line_id = prl.requisition_line_id(+);
SELECT COUNT(1) INTO l_count FROM zx_validation_errors_gt;
UPDATE zx_transaction_lines_gt zxlgt
SET zxlgt.product_type =
(SELECT DECODE(prl.purchase_basis,
'GOODS', 'GOODS',
'SERVICES')
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = zxlgt.trx_line_id)
WHERE zxlgt.product_type IS NULL
AND zxlgt.line_level_action = 'CREATE';
SELECT
'CALCULATE_TAX' --error_level,
,PO_CONSTANTS_SV.REQUISITION --document_type_code,
,p_requisition_header_id --document_id,
,prh.segment1 --document_num,
,zxegt.trx_line_id --line_id,
,prl.line_num --line_num,
,null --line_location_id,
,null --shipment_num,
,null --distribution_id,
,null --distribution_num,
,zxegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_errors_gt zxegt, po_requisition_headers_all prh,
po_requisition_lines_all prl
WHERE zxegt.trx_id = prh.requisition_header_id
AND zxegt.trx_line_id = prl.requisition_line_id(+);
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
SELECT COUNT(1) INTO l_count FROM zx_itm_distributions_gt;
PO_LOG.stmt(d_module_base,d_progress,'Exception while selecting from zx_itm_distributions_gt');
UPDATE zx_trx_headers_gt
SET event_type_code = PO_CONSTANTS_SV.REQ_DISTRIBUTED;
SELECT
'DETERMINE_RECOVERY' --error_level,
,PO_CONSTANTS_SV.REQUISITION --document_type_code,
,p_requisition_header_id --document_id,
,prh.segment1 --document_num,
,zxegt.trx_line_id --line_id,
,prl.line_num --line_num,
,null --line_location_id,
,null --shipment_num,
,zxegt.trx_line_dist_id --distribution_id,
,prd.distribution_id --distribution_num,
,zxegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_errors_gt zxegt, po_requisition_headers_all prh,
po_requisition_lines_all prl, po_req_distributions_all prd
WHERE zxegt.trx_id = prh.requisition_header_id
AND zxegt.trx_line_id = prl.requisition_line_id(+)
AND zxegt.trx_line_dist_id = prd.distribution_id(+);
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
UPDATE po_req_distributions_all prd
SET prd.recoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist_gt zxdist
WHERE zxdist.trx_line_dist_id = prd.distribution_id
AND zxdist.recoverable_flag = 'Y'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE)
,prd.nonrecoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist_gt zxdist
WHERE zxdist.trx_line_dist_id = prd.distribution_id
AND zxdist.recoverable_flag = 'N'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE)
WHERE prd.distribution_id IN
(SELECT trx_line_dist_id FROM zx_itm_distributions_gt);
SELECT prh.org_id
INTO l_org_id
FROM po_requisition_headers_all prh
WHERE prh.requisition_header_id=p_requisition_header_id;
l_trx_rec.event_type_code := PO_CONSTANTS_SV.REQ_DELETED;
ZX_API_PUB.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_rec => l_trx_rec);
ZX_API_PUB.insert_line_det_factors(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PO_LOG.stmt(d_module_base,d_progress,'insert_line_det_factors returned with status '||l_return_status);
update po_requisition_headers_all
set tax_attribute_update_code = null
where requisition_header_id = p_requisition_header_id;
update po_requisition_lines_all
set tax_attribute_update_code = null
where requisition_header_id = p_requisition_header_id;
SELECT COUNT(1) INTO l_count FROM zx_itm_distributions_gt;
SELECT
'DETERMINE_RECOVERY' --error_level,
,PO_CONSTANTS_SV.PO --document_type_code,
,p_po_header_id --document_id,
,ph.segment1 --document_num,
,null --line_id,
,null --line_num,
,zxegt.trx_line_id --line_location_id,
,pll.shipment_num --shipment_num,
,zxegt.trx_line_dist_id --distribution_id,
,pd.distribution_num --distribution_num,
,zxegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_errors_gt zxegt, po_headers_all ph,
po_line_locations_all pll, po_distributions_all pd
WHERE zxegt.trx_id = ph.po_header_id
AND zxegt.trx_line_id = pll.line_location_id(+)
AND zxegt.trx_line_dist_id = pd.po_distribution_id(+);
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
UPDATE po_distributions_all pd
SET pd.recoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'Y'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE)
,pd.nonrecoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'N'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE)
WHERE pd.po_distribution_id IN
(SELECT trx_line_dist_id FROM zx_itm_distributions_gt);
UPDATE po_line_locations_all pll
SET pll.taxable_flag =
DECODE((SELECT COUNT(1) FROM zx_lines zl
WHERE zl.trx_line_id = pll.line_location_id
AND zl.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND ( (zl.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_header_id)
OR
(zl.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_release_id))),
0, 'N',
'Y'
)
WHERE
pll.line_location_id IN (SELECT line_location_id FROM po_distributions_all pd,zx_itm_distributions_gt zi WHERE pd.po_distribution_id=zi.trx_line_dist_id);
UPDATE po_distributions_all
SET tax_attribute_update_code = null
WHERE po_distribution_id IN (SELECT trx_line_dist_id
FROM zx_itm_distributions_gt);
SELECT COUNT(1) INTO l_count FROM zx_itm_distributions_gt;
SELECT
'DETERMINE_RECOVERY' --error_level,
,PO_CONSTANTS_SV.PO --document_type_code,
,p_po_release_id --document_id,
,ph.segment1 --document_num,
,null --line_id,
,null --line_num,
,zxegt.trx_line_id --line_location_id,
,pll.shipment_num --shipment_num,
,zxegt.trx_line_dist_id --distribution_id,
,pd.distribution_num --distribution_num,
,zxegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_errors_gt zxegt, po_releases_all pr, po_headers_all ph,
po_line_locations_all pll, po_distributions_all pd
WHERE zxegt.trx_id = pr.po_release_id
AND pr.po_header_id = ph.po_header_id
AND zxegt.trx_line_id = pll.line_location_id(+)
AND zxegt.trx_line_dist_id = pd.po_distribution_id(+);
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
UPDATE po_distributions_all pd
SET pd.recoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'Y'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE)
,pd.nonrecoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist zxdist
WHERE zxdist.trx_line_dist_id = pd.po_distribution_id
AND zxdist.recoverable_flag = 'N'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE)
WHERE pd.po_distribution_id IN
(SELECT trx_line_dist_id FROM zx_itm_distributions_gt);
UPDATE po_line_locations_all pll
SET pll.taxable_flag =
DECODE((SELECT COUNT(1) FROM zx_lines zl
WHERE zl.trx_line_id = pll.line_location_id
AND zl.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND ( (zl.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_header_id)
OR
(zl.entity_code = PO_CONSTANTS_SV.REL_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE
AND zl.trx_id = pll.po_release_id))),
0, 'N',
'Y'
)
WHERE
pll.line_location_id IN (SELECT line_location_id FROM po_distributions_all pd,zx_itm_distributions_gt zi WHERE pd.po_distribution_id=zi.trx_line_dist_id);
UPDATE po_distributions_all
SET tax_attribute_update_code = null
WHERE po_distribution_id IN (SELECT trx_line_dist_id
FROM zx_itm_distributions_gt);
SELECT COUNT(1) INTO l_count FROM zx_itm_distributions_gt;
SELECT
'DETERMINE_RECOVERY' --error_level,
,PO_CONSTANTS_SV.REQUISITION --document_type_code,
,p_requisition_header_id --document_id,
,prh.segment1 --document_num,
,zxegt.trx_line_id --line_id,
,prl.line_num --line_num,
,null --line_location_id,
,null --shipment_num,
,zxegt.trx_line_dist_id --distribution_id,
,prd.distribution_num --distribution_num,
,zxegt.message_text --message_text
BULK COLLECT INTO G_TAX_ERRORS_TBL
FROM zx_errors_gt zxegt, po_requisition_headers_all prh,
po_requisition_lines_all prl, po_req_distributions_all prd
WHERE zxegt.trx_id = prh.requisition_header_id
AND zxegt.trx_line_id = prl.requisition_line_id(+)
AND zxegt.trx_line_dist_id = prd.distribution_id(+);
SELECT COUNT(1) INTO l_count FROM zx_errors_gt;
UPDATE po_req_distributions_all prd
SET prd.recoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist_gt zxdist
WHERE zxdist.trx_line_dist_id = prd.distribution_id
AND zxdist.recoverable_flag = 'Y'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE)
,prd.nonrecoverable_tax =
(SELECT SUM(zxdist.rec_nrec_tax_amt)
FROM zx_rec_nrec_dist_gt zxdist
WHERE zxdist.trx_line_dist_id = prd.distribution_id
AND zxdist.recoverable_flag = 'N'
AND zxdist.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zxdist.entity_code = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxdist.event_class_code = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE)
WHERE prd.distribution_id IN
(SELECT trx_line_dist_id FROM zx_itm_distributions_gt);
INSERT INTO zx_trx_headers_gt(
internal_organization_id
,application_id
,entity_code
,event_class_code
,event_type_code
,trx_id
,trx_date
,trx_doc_revision
,ledger_id
,trx_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,minimum_accountable_unit --Bug 5474336. Pass mau to EBTax
,precision
,legal_entity_id
,rounding_ship_from_party_id
,default_taxation_country
,quote_flag
,trx_number
,trx_description
,trx_communicated_date
,document_sub_type
,provnl_tax_determination_date
-- Bug 5025018. Updated tax attribute mappings
,rounding_bill_to_party_id
,rndg_ship_from_party_site_id
)
SELECT
ph.org_id --internal_organization_id
,PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.PO_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE --event_class_code
,DECODE(ph.tax_attribute_update_code, --event_type_code
'CREATE', PO_CONSTANTS_SV.PO_CREATED,
'COPY_AND_CREATE', PO_CONSTANTS_SV.PO_CREATED,
PO_CONSTANTS_SV.PO_ADJUSTED) --for null and 'UPDATE'
,ph.po_header_id --trx_id
,sysdate --trx_date
,ph.revision_num --trx_doc_revision
,(SELECT set_of_books_id --ledger_id
FROM financials_system_params_all WHERE org_id=ph.org_id)
,ph.currency_code --trx_currency_code
,ph.rate_date --currency_conversion_date
,ph.rate --currency_conversion_rate
,ph.rate_type --currency_conversion_type
--Bug 5474336. Pass mau to EBTax
,fc.minimum_accountable_unit --minimum_accountable_unit
,NVL(fc.precision, 2) --precision
,PO_CORE_S.get_default_legal_entity_id(ph.org_id) --legal_entity_id
,(SELECT pv.party_id FROM po_vendors pv --rounding_ship_from_party_id
WHERE pv.vendor_id=ph.vendor_id)
,DECODE(p_calling_program, --default_taxation_country
'COPY_DOCUMENT', null,
zxldet.default_taxation_country)
,'N' --quote_flag
,ph.segment1 --trx_number
,ph.comments --trx_description
,sysdate --ph.print_date --trx_communicated_date
,DECODE(p_calling_program, --document_sub_type
'COPY_DOCUMENT', null,
zxldet.document_sub_type)
,DECODE(ph.document_creation_method, --provnl_tax_determination_date
'CREATE_CONSUMPTION',
(SELECT pll.need_by_date
FROM po_line_locations_all pll
WHERE pll.po_header_id=ph.po_header_id
AND pll.need_by_date IS NOT NULL
AND rownum=1),
null)
-- Bug 5025018. Updated tax attribute mappings
,ph.org_id --rounding_bill_to_party_id
,(SELECT pvs.party_site_id from po_vendor_sites_all pvs --rndg_ship_from_party_site_id
WHERE pvs.vendor_site_id=ph.vendor_site_id)
FROM po_headers_all ph, zx_lines_det_factors zxldet, fnd_currencies fc
WHERE ph.po_header_id = p_po_header_id_tbl(i)
AND fc.currency_code = ph.currency_code
-- Conditions for getting Additional Tax Attributes
-- Note that the po_header_id is of current document being processed,
-- not of any source document. Get the first row obtained from join
-- with zx_lines_det_factors because that table is denormalized
AND zxldet.trx_id(+) = ph.po_header_id
AND zxldet.application_id(+) = PO_CONSTANTS_SV.APPLICATION_ID
AND zxldet.entity_code(+) = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxldet.event_class_code(+) = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxldet.trx_level_type(+) = PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE
AND rownum = 1;
INSERT INTO zx_trx_headers_gt(
internal_organization_id
,application_id
,entity_code
,event_class_code
,event_type_code
,trx_id
,trx_date
,trx_doc_revision
,ledger_id
,trx_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,minimum_accountable_unit --Bug 5474336. Pass mau to EBTax
,precision
,legal_entity_id
,rounding_ship_from_party_id
,default_taxation_country
,quote_flag
,trx_number
,trx_description
,trx_communicated_date
,document_sub_type
,provnl_tax_determination_date
-- Bug 5025018. Updated tax attribute mappings
,rounding_bill_to_party_id
,rndg_ship_from_party_site_id
)
SELECT
ph.org_id --internal_organization_id
,PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.PO_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE --event_class_code
,PO_CONSTANTS_SV.PO_REDISTRIBUTED --event_type_code
,ph.po_header_id --trx_id
,sysdate --trx_date
,ph.revision_num --trx_doc_revision
,(SELECT set_of_books_id --ledger_id
FROM financials_system_params_all WHERE org_id=ph.org_id)
,ph.currency_code --trx_currency_code
,ph.rate_date --currency_conversion_date
,ph.rate --currency_conversion_rate
,ph.rate_type --currency_conversion_type
--Bug 5474336. Pass mau to EBTax
,fc.minimum_accountable_unit --minimum_accountable_unit
,NVL(fc.precision, 2) --precision
,PO_CORE_S.get_default_legal_entity_id(ph.org_id) --legal_entity_id
,(SELECT pv.party_id FROM po_vendors pv --rounding_ship_from_party_id
WHERE pv.vendor_id=ph.vendor_id)
,null --default_taxation_country
,'N' --quote_flag
,ph.segment1 --trx_number
,ph.comments --trx_description
,sysdate --ph.print_date --trx_communicated_date
,null --document_sub_type
,DECODE(ph.document_creation_method, --provnl_tax_determination_date
'CREATE_CONSUMPTION',
(SELECT pll.need_by_date
FROM po_line_locations_all pll
WHERE pll.po_header_id=ph.po_header_id
AND pll.need_by_date IS NOT NULL
AND rownum=1),
null)
-- Bug 5025018. Updated tax attribute mappings
,ph.org_id --rounding_bill_to_party_id
,(SELECT pvs.party_site_id from po_vendor_sites_all pvs --rndg_ship_from_party_site_id
WHERE pvs.vendor_site_id=ph.vendor_site_id)
FROM po_headers_all ph, fnd_currencies fc
WHERE ph.po_header_id = p_po_header_id
AND fc.currency_code = ph.currency_code;
INSERT INTO zx_trx_headers_gt(
internal_organization_id
,application_id
,entity_code
,event_class_code
,event_type_code
,trx_id
,trx_date
,trx_doc_revision
,ledger_id
,trx_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,minimum_accountable_unit --Bug 5474336. Pass mau to EBTax
,precision
,legal_entity_id
,rounding_ship_from_party_id
,default_taxation_country
,quote_flag
,trx_number
,trx_description
,trx_communicated_date
,document_sub_type
,provnl_tax_determination_date
-- Bug 5025018. Updated tax attribute mappings
,rounding_bill_to_party_id
,rndg_ship_from_party_site_id
)
SELECT
pr.org_id --internal_organization_id
,PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REL_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE --event_class_code
,DECODE(pr.tax_attribute_update_code, --event_type_code
'CREATE', PO_CONSTANTS_SV.REL_CREATED,
'COPY_AND_CREATE', PO_CONSTANTS_SV.REL_CREATED,
PO_CONSTANTS_SV.REL_ADJUSTED) -- for null and 'UPDATE'
,pr.po_release_id --trx_id
,sysdate --trx_date
,pr.revision_num --trx_doc_revision
,(select set_of_books_id --ledger_id
from financials_system_params_all where org_id=pr.org_id)
,ph.currency_code --trx_currency_code
,ph.rate_date --currency_conversion_date
,ph.rate --currency_conversion_rate
,ph.rate_type --currency_conversion_type
--Bug 5474336. Pass mau to EBTax
,fc.minimum_accountable_unit --minimum_accountable_unit
,NVL(fc.precision, 2) --precision
,PO_CORE_S.get_default_legal_entity_id(pr.org_id) --legal_entity_id
,(SELECT pv.party_id FROM po_vendors pv --rounding_ship_from_party_id
WHERE pv.vendor_id=ph.vendor_id)
,zxldet.default_taxation_country --default_taxation_country
,'N' --quote_flag
,ph.segment1 --trx_number
,null --trx_description
,sysdate --pr.print_date --trx_communicated_date
,zxldet.document_sub_type --document_sub_type
,DECODE(pr.document_creation_method, --provnl_tax_determination_date
'CREATE_CONSUMPTION',
(SELECT pll.need_by_date
FROM po_line_locations_all pll
WHERE pll.po_release_id=pr.po_release_id
AND pll.need_by_date IS NOT NULL
AND rownum=1),
null)
-- Bug 5025018. Updated tax attribute mappings
,pr.org_id --rounding_bill_to_party_id
,(SELECT pvs.party_site_id from po_vendor_sites_all pvs --rndg_ship_from_party_site_id
WHERE pvs.vendor_site_id=ph.vendor_site_id)
-- Using OUTER JOIN in FROM clause syntax here because (+) operator
-- is not flexible enough to be used inside an OR condition
FROM po_headers_all ph
,fnd_currencies fc
,po_releases_all pr
-- Conditions for getting Additional Tax Attributes
-- Copy from Planned PO if its a newly created Scheduled Release
-- ELSE simply copy from existing release header (ie. in case of a
-- shipment split or regular blanket/scheduled release create/update
LEFT OUTER JOIN zx_lines_det_factors zxldet
ON ((pr.po_header_id = zxldet.trx_id
AND PO_CONSTANTS_SV.APPLICATION_ID = zxldet.application_id
AND PO_CONSTANTS_SV.PO_ENTITY_CODE = zxldet.entity_code
AND PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE = zxldet.event_class_code
AND PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE = zxldet.trx_level_type
AND pr.release_type = PO_CONSTANTS_SV.SCHEDULED
AND pr.tax_attribute_update_code = 'CREATE')
OR
(pr.po_release_id = zxldet.trx_id
AND PO_CONSTANTS_SV.APPLICATION_ID = zxldet.application_id
AND PO_CONSTANTS_SV.REL_ENTITY_CODE = zxldet.entity_code
AND PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE = zxldet.event_class_code
AND PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE = zxldet.trx_level_type)
)
WHERE pr.po_release_id = p_po_release_id_tbl(i)
AND pr.po_header_id = ph.po_header_id
AND fc.currency_code = ph.currency_code
AND rownum = 1;
INSERT INTO zx_trx_headers_gt(
internal_organization_id
,application_id
,entity_code
,event_class_code
,event_type_code
,trx_id
,trx_date
,trx_doc_revision
,ledger_id
,trx_currency_code
,currency_conversion_date
,currency_conversion_rate
,currency_conversion_type
,minimum_accountable_unit --Bug 5474336. Pass mau to EBTax
,precision
,legal_entity_id
,rounding_ship_from_party_id
,default_taxation_country
,quote_flag
,trx_number
,trx_description
,trx_communicated_date
,document_sub_type
,provnl_tax_determination_date
-- Bug 5025018. Updated tax attribute mappings
,rounding_bill_to_party_id
,rndg_ship_from_party_site_id
)
SELECT
pr.org_id --internal_organization_id
,PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REL_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE --event_class_code
,PO_CONSTANTS_SV.REL_REDISTRIBUTED --event_type_code
,pr.po_release_id --trx_id
,sysdate --trx_date
,pr.revision_num --trx_doc_revision
,(select set_of_books_id --ledger_id
from financials_system_params_all where org_id=pr.org_id)
,ph.currency_code --trx_currency_code
,ph.rate_date --currency_conversion_date
,ph.rate --currency_conversion_rate
,ph.rate_type --currency_conversion_type
--Bug 5474336. Pass mau to EBTax
,fc.minimum_accountable_unit --minimum_accountable_unit
,NVL(fc.precision, 2) --precision
,PO_CORE_S.get_default_legal_entity_id(pr.org_id) --legal_entity_id
,(SELECT pv.party_id FROM po_vendors pv --rounding_ship_from_party_id
WHERE pv.vendor_id=ph.vendor_id)
,null --default_taxation_country
,'N' --quote_flag
,ph.segment1 --trx_number
,null --trx_description
,sysdate --pr.print_date --trx_communicated_date
,null --document_sub_type
,DECODE(pr.document_creation_method, --provnl_tax_determination_date
'CREATE_CONSUMPTION',
(SELECT pll.need_by_date
FROM po_line_locations_all pll
WHERE pll.po_release_id=pr.po_release_id
AND pll.need_by_date IS NOT NULL
AND rownum=1),
null)
-- Bug 5025018. Updated tax attribute mappings
,ph.org_id --rounding_bill_to_party_id
,(SELECT pvs.party_site_id from po_vendor_sites_all pvs --rndg_ship_from_party_site_id
WHERE pvs.vendor_site_id=ph.vendor_site_id)
FROM po_releases_all pr, po_headers_all ph, fnd_currencies fc
WHERE pr.po_release_id = p_po_release_id
AND pr.po_header_id = ph.po_header_id
AND fc.currency_code = ph.currency_code;
INSERT INTO zx_trx_headers_gt(
internal_organization_id
,application_id
,entity_code
,event_class_code
,event_type_code
,trx_id
,trx_date
,ledger_id
,legal_entity_id
,rounding_bill_to_party_id
,quote_flag
,document_sub_type
,default_taxation_country
,icx_session_id
)
SELECT
prh.org_id --internal_organization_id
,PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REQ_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE --event_class_code
,DECODE(p_calling_program, --event_type_code
--If calling determine_recovery from ATI page flow
'DETERMINE_RECOVERY_REQ', PO_CONSTANTS_SV.REQ_DISTRIBUTED,
--Regular UI flow
PO_CONSTANTS_SV.REQ_CREATED)
,prh.requisition_header_id --trx_id
,sysdate --trx_date
,(select set_of_books_id --ledger_id
from financials_system_params_all where org_id=prh.org_id)
,PO_CORE_S.get_default_legal_entity_id(prh.org_id) --legal_entity_id
,prh.org_id --rounding_bill_to_party_id
,'Y' --quote_flag
,zxldet.document_sub_type --document_sub_type
,zxldet.default_taxation_country --default_taxation_country
,DECODE(p_calling_program, --icx_session_id
'DETERMINE_RECOVERY_REQ', null,
FND_GLOBAL.session_id)
FROM po_requisition_headers_all prh, zx_lines_det_factors zxldet
WHERE prh.requisition_header_id = p_requisition_header_id
-- Conditions for getting Additional Tax Attributes
-- Note that the req_header_id is of current document being processed,
-- not of any source document. Get the first row obtained from join
-- with zx_lines_det_factors because that table is denormalized
AND zxldet.trx_id(+) = prh.requisition_header_id
AND zxldet.application_id(+) = PO_CONSTANTS_SV.APPLICATION_ID
AND zxldet.entity_code(+) = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxldet.event_class_code(+) = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE
AND zxldet.trx_level_type(+) = PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE
AND rownum = 1;
INSERT INTO zx_transaction_lines_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_level_type
,trx_line_id
,line_level_action
,line_class
,trx_line_type
,trx_line_date
,trx_business_category
,line_intended_use
,user_defined_fisc_class
,line_amt
,trx_line_quantity
,product_id
,product_org_id
,product_fisc_classification
,uom_code
,product_type
,product_code
,product_category
,fob_point
,ship_from_party_id
,bill_from_party_id
,ship_from_party_site_id
,bill_from_party_site_id
,ship_to_location_id
,ship_from_location_id
,bill_to_location_id
,bill_from_location_id /* 6524317 */
,account_ccid
,ref_doc_application_id
,ref_doc_entity_code
,ref_doc_event_class_code
,ref_doc_trx_id
,ref_doc_line_id
,line_trx_user_key1
,line_trx_user_key2
-- Bug 5079867. Ordering of complex work payment lines in ATI page
,line_trx_user_key3
,trx_line_number
,trx_line_description
,product_description
,assessable_value
,line_amt_includes_tax_flag
,input_tax_classification_code
,source_application_id
,source_entity_code
,source_event_class_code
,source_trx_id
,source_line_id
,source_trx_level_type
,unit_price
,ref_doc_trx_level_type
-- Bug 5025018. Updated tax attribute mappings
,ship_third_pty_acct_id
,bill_third_pty_acct_id
,ship_third_pty_acct_site_id
,bill_third_pty_acct_site_id
,ship_to_party_id
,user_upd_det_factors_flag --Bug 5632300
,defaulting_attribute1 --Bug#6902111
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.PO_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE --event_class_code
,ph.po_header_id --trx_id
,PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE --trx_level_type
,pll.line_location_id --trx_line_id
,pll.tax_attribute_update_code --line_level_action
,DECODE(pll.shipment_type,--line_class
'PREPAYMENT', DECODE(pll.payment_type,
'ADVANCE', 'ADVANCE',
'FINANCING'),
DECODE(pll.value_basis,
'QUANTITY', 'INVOICE',
'AMOUNT')
)
,'ITEM' --trx_line_type
,COALESCE(pll.need_by_date, pll.promised_date, sysdate) --trx_line_date
,DECODE(p_calling_program, --trx_business_category
'COPY_DOCUMENT', null,
zxldet.trx_business_category)
,DECODE(p_calling_program, --line_intended_use
'COPY_DOCUMENT', null,
zxldet.line_intended_use)
,DECODE(p_calling_program, --user_defined_fisc_class
'COPY_DOCUMENT', null,
zxldet.user_defined_fisc_class)
,NVL(pll.amount, pll.price_override*pll.quantity) --line_amt
,pll.quantity --trx_line_quantity
,pl.item_id --product_id
-- Bug 5335818. Pass in inventory_organization_id
,(SELECT fsp.inventory_organization_id --product_org_id
FROM financials_system_params_all fsp
WHERE fsp.org_id=pll.org_id)
,DECODE(p_calling_program, --product_fisc_classification
'COPY_DOCUMENT', null,
zxldet.product_fisc_classification)
,(SELECT mum.uom_code FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure=pll.unit_meas_lookup_code) --uom_code
,DECODE(p_calling_program, --product_type
'COPY_DOCUMENT', null,
zxldet.product_type)
,msib.segment1 --product_code
,DECODE(p_calling_program, --product_category
'COPY_DOCUMENT', null,
zxldet.product_category)
,ph.fob_lookup_code --fob_point
,pv.party_id --ship_from_party_id
,pv.party_id --bill_from_party_id
,pvs.party_site_id --ship_from_party_site_id
,pvs.party_site_id --bill_from_party_site_id
,pll.ship_to_location_id --ship_to_location_id
,(SELECT hzps.location_id --ship_from_location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = pvs.party_site_id)
,ph.bill_to_location_id --bill_to_location_id
,(SELECT pvs.location_id from po_vendor_sites_all pvs /* 6524317 - Passing Location Id as Bill From Location Id */
WHERE pvs.vendor_site_id=ph.vendor_site_id)
-- Get account id from first distribution, if created
-- otherwise from the expense account of the item
,NVL((SELECT pd.code_combination_id --account_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id = pll.line_location_id
AND rownum = 1),
msib.expense_account
)
,null --ref_doc_application_id
,null --ref_doc_entity_code
,null --ref_doc_event_class_code
,null --ref_doc_trx_id
,null --ref_doc_line_id
,pl.line_num --line_trx_user_key1
,PO_LOCATIONS_S.get_location_code(pll.ship_to_location_id) --line_trx_user_key2
-- Bug 5079867. Ordering of complex work payment lines in ATI page
,DECODE(pll.payment_type, null, 0, --line_trx_user_key3
'DELIVERY', 1,
'ADVANCE', 2, 3)
,DECODE(pll.payment_type, --trx_line_number
'ADVANCE', null,
'DELIVERY', null,
pll.shipment_num)
,DECODE(pll.shipment_type, --trx_line_description
'STANDARD', DECODE(pll.payment_type,
null, pl.item_description, --non complex work Standard PO
pll.description --complex work Standard PO
),
pl.item_description -- for shipment_type='PLANNED'
)
,DECODE(pll.shipment_type, --product_description
'STANDARD', DECODE(pll.payment_type,
null, pl.item_description, --non complex work Standard PO
pll.description --complex work Standard PO
),
pl.item_description -- for shipment_type='PLANNED'
)
,DECODE(p_calling_program, --assessable_value
'COPY_DOCUMENT', null,
zxldet.assessable_value)
,'N' --line_amt_includes_tax_flag
-- Only newly added lines can come in through PDOI (no updated
-- lines), so tax_name can be populated to override.
-- Once the new line has been created, if it is modified
-- through any other program, tax_name field will be populated
-- but will not get populated as overridden tax_classification
,DECODE(p_calling_program, --input_tax_classification_code
'PDOI', pll.tax_name,
'COPY_DOCUMENT', null,
zxldet.input_tax_classification_code)
-- Bug 7337548
-- Copying requisition information as Source Document Information
-- in case of Autocreate. This information will be used to copy
-- Tax attributes when the attributes are overridden in requisition.
-- Calling program for autocreate is 'POXBWVRP_PO','AUTOCREATED_DOC_WF'
-- and 'PORELGEB'
,DECODE(p_calling_program, --source_application_id
'COPY_DOCUMENT', PO_CONSTANTS_SV.APPLICATION_ID,
'POXBWVRP_PO', PO_CONSTANTS_SV.APPLICATION_ID,
'AUTOCREATED_DOC_WF' ,PO_CONSTANTS_SV.APPLICATION_ID,
'PORELGEB',PO_CONSTANTS_SV.APPLICATION_ID,
null)
,DECODE(p_calling_program, --source_entity_code
'COPY_DOCUMENT', PO_CONSTANTS_SV.PO_ENTITY_CODE,
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
'PORELGEB', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
null)
,DECODE(p_calling_program, --source_event_class_code
'COPY_DOCUMENT', PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
'PORELGEB', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
null)
,DECODE(p_calling_program, -- source_trx_id
'COPY_DOCUMENT', (SELECT source_shipment.po_header_id --source_trx_id
FROM po_line_locations_all source_shipment
WHERE source_shipment.line_location_id = pll.original_shipment_id
AND p_calling_program = 'COPY_DOCUMENT'),
'POXBWVRP_PO', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'POXBWVRP_PO'),
'AUTOCREATED_DOC_WF', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'AUTOCREATED_DOC_WF'),
'PORELGEB', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'PORELGEB'),
null)
,DECODE(p_calling_program, --source_line_id
'COPY_DOCUMENT', pll.original_shipment_id,
'POXBWVRP_PO',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'POXBWVRP_PO'),
'AUTOCREATED_DOC_WF',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'AUTOCREATED_DOC_WF'),
'PORELGEB',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'PORELGEB'),
null)
,DECODE(p_calling_program, --source_trx_level_type
'COPY_DOCUMENT', PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE,
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
'PORELGEB', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
null)
,pll.price_override --unit_price
,null --ref_doc_trx_level_type
-- Bug 5025018. Updated tax attribute mappings
,pv.vendor_id --ship_third_pty_acct_id
,pv.vendor_id --bill_third_pty_acct_id
,pvs.vendor_site_id --ship_third_pty_acct_site_id
,pvs.vendor_site_id --bill_third_pty_acct_site_id
,pll.ship_to_organization_id --ship_to_party_id
--Bug 5632300. Parameter to confirm that tax classification is overridden
,(SELECT 'Y' FROM DUAL --user_upd_det_factors_flag
WHERE p_calling_program = 'PDOI'
AND pll.tax_name IS NOT NULL)
,pll.ship_to_organization_id --Bug#6902111
FROM po_headers_all ph, po_lines_all pl, po_line_locations_all pll,
zx_lines_det_factors zxldet, po_vendors pv,
po_vendor_sites_all pvs, mtl_system_items_b msib
WHERE ph.po_header_id = pll.po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.tax_attribute_update_code IS NOT NULL
AND pll.tax_attribute_update_code <> 'DIST_DELETE'
AND ph.po_header_id = p_po_header_id_tbl(i)
-- Conditions for getting Additional Tax Attributes
-- Do not put a condition on zxldet.trx_id here because that would
-- entail bringing the source_header_id for the shipment being currently
-- processed. Join with trx_line_id itself is unique because the document
-- type has been classified with event_class_code and line_location_id
-- will always be unique whether PO shipment or Release shipment
AND zxldet.application_id(+) = PO_CONSTANTS_SV.APPLICATION_ID
AND zxldet.entity_code(+) = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zxldet.event_class_code(+) = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zxldet.trx_level_type(+) = PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE
AND zxldet.trx_line_id(+) = pll.original_shipment_id
-- Join with vendor tables to get party and party site information
AND pv.vendor_id(+) = ph.vendor_id
AND pvs.vendor_site_id(+) = ph.vendor_site_id
-- Join with items table for item information
AND msib.inventory_item_id(+) = pl.item_id
AND msib.organization_id(+) = pl.org_id;
INSERT INTO zx_transaction_lines_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_level_type
,trx_line_id
,line_level_action
,line_class
,trx_line_type
,trx_line_date
,trx_business_category
,line_intended_use
,user_defined_fisc_class
,line_amt
,trx_line_quantity
,product_id
,product_org_id
,product_fisc_classification
,uom_code
,product_type
,product_code
,product_category
,fob_point
,ship_from_party_id
,bill_from_party_id
,ship_from_party_site_id
,bill_from_party_site_id
,ship_to_location_id
,ship_from_location_id
,bill_to_location_id
,bill_from_location_id /* 6524317 */
,account_ccid
,ref_doc_application_id
,ref_doc_entity_code
,ref_doc_event_class_code
,ref_doc_trx_id
,ref_doc_line_id
,line_trx_user_key1
,line_trx_user_key2
,trx_line_number
,trx_line_description
,product_description
,assessable_value
,line_amt_includes_tax_flag
,input_tax_classification_code
,source_application_id
,source_entity_code
,source_event_class_code
,source_trx_id
,source_line_id
,source_trx_level_type
,unit_price
,ref_doc_trx_level_type
-- Bug 5025018. Updated tax attribute mappings
,ref_doc_line_quantity
,ship_third_pty_acct_id
,bill_third_pty_acct_id
,ship_third_pty_acct_site_id
,bill_third_pty_acct_site_id
,ship_to_party_id
,defaulting_attribute1 --Bug#6902111
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REL_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE --event_class_code
,pr.po_release_id --trx_id
,PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE --trx_level_type
,pll.line_location_id --trx_line_id
,pll.tax_attribute_update_code --line_level_action
,DECODE(pll.shipment_type,--line_class
'PREPAYMENT', DECODE(pll.payment_type,
'ADVANCE', 'ADVANCE',
'FINANCING'),
DECODE(pll.value_basis,
'QUANTITY', 'INVOICE',
'AMOUNT')
)
,'ITEM' --trx_line_type
,COALESCE(pll.need_by_date, pll.promised_date, sysdate) --trx_line_date
,zxldet.trx_business_category --trx_business_category
,zxldet.line_intended_use --line_intended_use
,zxldet.user_defined_fisc_class --user_defined_fisc_class
,nvl(pll.amount, pll.price_override*pll.quantity) --line_amt
,pll.quantity --trx_line_quantity
,pl.item_id --product_id
-- Bug 5335818. Pass in inventory_organization_id
,(SELECT fsp.inventory_organization_id --product_org_id
FROM financials_system_params_all fsp
WHERE fsp.org_id=pll.org_id)
,zxldet.product_fisc_classification --product_fisc_classification
,(SELECT mum.uom_code FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure=pll.unit_meas_lookup_code) --uom_code
,zxldet.product_type --product_type
,msib.segment1 --product_code
,zxldet.product_category --product_category
,ph.fob_lookup_code --fob_point
,pov.party_id --ship_from_party_id
,pov.party_id --bill_from_party_id
,pvs.party_site_id --ship_from_party_site_id
,pvs.party_site_id --bill_from_party_site_id
,pll.ship_to_location_id --ship_to_location_id
,(SELECT hzps.location_id --ship_from_location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = pvs.party_site_id)
,ph.bill_to_location_id --bill_to_location_id
,(SELECT pvs.location_id from po_vendor_sites_all pvs /* 6524317 - Passing Location Id as Bill From Location Id */
WHERE pvs.vendor_site_id=ph.vendor_site_id)
-- Get account id from first distribution, if created
-- otherwise from the expense account of the item
,NVL((SELECT pd.code_combination_id --account_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id = pll.line_location_id
AND rownum = 1),
msib.expense_account
)
-- If scheduled release, pass Planned PO as a reference
,DECODE(pr.release_type, --ref_doc_application_id
PO_CONSTANTS_SV.SCHEDULED, PO_CONSTANTS_SV.APPLICATION_ID,
null)
,DECODE(pr.release_type, --ref_doc_entity_code
PO_CONSTANTS_SV.SCHEDULED, PO_CONSTANTS_SV.PO_ENTITY_CODE,
null)
,DECODE(pr.release_type, --ref_doc_event_class_code
PO_CONSTANTS_SV.SCHEDULED, PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
null)
,DECODE(pr.release_type, --ref_doc_trx_id
PO_CONSTANTS_SV.SCHEDULED, pr.po_header_id,
null)
,DECODE(pr.release_type, --ref_doc_line_id
PO_CONSTANTS_SV.SCHEDULED, pll.po_line_id,
null)
,pl.line_num --line_trx_user_key1
,PO_LOCATIONS_S.get_location_code(pll.ship_to_location_id) --line_trx_user_key2
,DECODE(pll.payment_type, --trx_line_number
'ADVANCE', null,
'DELIVERY', null,
pll.shipment_num)
,DECODE(pll.shipment_type, --trx_line_description
'STANDARD', DECODE(pll.payment_type,
null, pl.item_description, --non complex work Standard PO
pll.description --complex work Standard PO
),
pl.item_description -- for shipment_type='PLANNED'
)
,DECODE(pll.shipment_type, --product_description
'STANDARD', DECODE(pll.payment_type,
null, pl.item_description, --non complex work Standard PO
pll.description --complex work Standard PO
),
pl.item_description -- for shipment_type='PLANNED'
)
,zxldet.assessable_value --assessable_value
,'N' --line_amt_includes_tax_flag
-- Releases cannot be created through PDOI
,zxldet.input_tax_classification_code --input_tax_classification_code
-- Bug 7337548
-- Copying requisition information as Source Document Information
-- in case of Autocreate. This information will be used to copy
-- Tax attributes when the attributes are overridden in requisition.
-- Calling program for autocreate is 'POXBWVRP_PO','AUTOCREATED_DOC_WF'
-- and 'PORELGEB'
,DECODE(p_calling_program, --source_application_id
'POXBWVRP_PO', PO_CONSTANTS_SV.APPLICATION_ID,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.APPLICATION_ID,
'PORELGEB', PO_CONSTANTS_SV.APPLICATION_ID,
null)
,DECODE(p_calling_program, --source_entity_code
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
'PORELGEB', PO_CONSTANTS_SV.REQ_ENTITY_CODE,
null)
,DECODE(p_calling_program, --source_event_class_code
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
'PORELGEB', PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE,
null)
,DECODE(p_calling_program, -- source_trx_id
'POXBWVRP_PO', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'POXBWVRP_PO'),
'AUTOCREATED_DOC_WF', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'AUTOCREATED_DOC_WF'),
'PORELGEB', (SELECT requisition_header_id FROM
po_requisition_lines_all
WHERE requisition_line_id IN (SELECT Min(requisition_line_id) --source_trx_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id)
AND p_calling_program = 'PORELGEB'),
null)
,DECODE(p_calling_program, --source_line_id
'POXBWVRP_PO',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'POXBWVRP_PO'),
'AUTOCREATED_DOC_WF',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'AUTOCREATED_DOC_WF'),
'PORELGEB',(SELECT Min(requisition_line_id) --source_line_id
FROM po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND p_calling_program = 'PORELGEB'),
null)
,DECODE(p_calling_program, --source_trx_level_type
'POXBWVRP_PO', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
'AUTOCREATED_DOC_WF', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
'PORELGEB', PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE ,
null)
,pll.price_override --unit_price
,DECODE(pr.release_type, --ref_doc_trx_level_type
PO_CONSTANTS_SV.SCHEDULED, PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE,
null)
-- Bug 5025018. Updated tax attribute mappings
,DECODE(pr.release_type, --ref_doc_line_quantity
PO_CONSTANTS_SV.SCHEDULED, pll.quantity,
null)
,pov.vendor_id --ship_third_pty_acct_id
,pov.vendor_id --bill_third_pty_acct_id
,pvs.vendor_site_id --ship_third_pty_acct_site_id
,pvs.vendor_site_id --bill_third_pty_acct_site_id
,pll.ship_to_organization_id --ship_to_party_id
,pll.ship_to_organization_id --Bug#6902111
-- Using OUTER JOIN in FROM clause syntax here because (+) operator
-- is not flexible enough to be used inside an OR condition
FROM po_releases_all pr
,po_headers_all ph
-- Join with vendor tables to get party and party site information
LEFT OUTER JOIN po_vendors pov ON (ph.vendor_id = pov.vendor_id)
LEFT OUTER JOIN po_vendor_sites_all pvs
ON (ph.vendor_site_id = pvs.vendor_site_id)
,po_lines_all pl--Blanket/Scheduled header and line
-- Join with items table for item information
LEFT OUTER JOIN mtl_system_items_b msib
ON (pl.item_id = msib.inventory_item_id
AND pl.org_id = msib.organization_id)
,po_line_locations_all pll
-- Conditions for getting Additional Tax Attributes
-- Do not put a condition on zxldet.trx_id here because that would
-- entail bringing the source_header_id for the shipment being
-- currently processed. Join with trx_line_id itself is unique
-- because the document type has been classified with
-- event_class_code and line_location_id will always be unique
-- whether PO shipment or Release shipment
LEFT OUTER JOIN zx_lines_det_factors zxldet ON
((PO_CONSTANTS_SV.APPLICATION_ID = zxldet.application_id
AND PO_CONSTANTS_SV.REL_ENTITY_CODE = zxldet.entity_code
AND PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE = zxldet.event_class_code
AND PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE = zxldet.trx_level_type
AND pll.original_shipment_id = zxldet.trx_line_id
AND pll.original_shipment_id IS NOT NULL)
OR
(PO_CONSTANTS_SV.APPLICATION_ID = zxldet.application_id
AND PO_CONSTANTS_SV.PO_ENTITY_CODE = zxldet.entity_code
AND PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE = zxldet.event_class_code
AND PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE = zxldet.trx_level_type
AND pll.source_shipment_id = zxldet.trx_line_id
AND pll.shipment_type = PO_CONSTANTS_SV.SCHEDULED
AND pll.tax_attribute_update_code = 'CREATE'
AND pll.original_shipment_id IS NULL)
)
WHERE pr.po_release_id = pll.po_release_id
AND pll.tax_attribute_update_code IS NOT NULL
AND pll.tax_attribute_update_code <> 'DIST_DELETE'
AND pr.po_release_id = p_po_release_id_tbl(i)
AND ph.po_header_id = pr.po_header_id
AND pl.po_line_id = pll.po_line_id;
SELECT sob.currency_code, fsp.set_of_books_id, psp.default_rate_type
INTO l_functional_currency_code, l_set_of_books_id, l_rate_type
FROM financials_system_params_all fsp, gl_sets_of_books sob,
po_requisition_headers_all prh, po_system_parameters_all psp
WHERE fsp.set_of_books_id = sob.set_of_books_id
AND fsp.org_id = prh.org_id
AND prh.requisition_header_id = p_requisition_header_id
AND psp.org_id = prh.org_id;
INSERT INTO zx_transaction_lines_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_level_type
,trx_line_id
,line_class
,line_level_action
,trx_line_type
,trx_line_date
,line_amt_includes_tax_flag
,line_amt
,trx_line_quantity
,unit_price
,product_id
,product_org_id
,uom_code
,product_code
,ship_to_party_id
,ship_from_party_id
,bill_to_party_id
,bill_from_party_id
,ship_from_party_site_id
,bill_from_party_site_id
,ship_to_location_id
,ship_from_location_id
,bill_to_location_id
,ship_third_pty_acct_id
,ship_third_pty_acct_site_id
,historical_flag
,trx_line_currency_code
,trx_line_currency_conv_date
,trx_line_currency_conv_rate
,trx_line_currency_conv_type
,trx_line_mau
,trx_line_precision
,historical_tax_code_id
,trx_business_category
,product_category
,product_fisc_classification
,line_intended_use
,product_type
,user_defined_fisc_class
,assessable_value
,input_tax_classification_code
,account_ccid
-- Bug 5025018. Updated tax attribute mappings
,bill_third_pty_acct_id
,bill_third_pty_acct_site_id
-- Bug 5079867. Line number and description in ATI page
,trx_line_number
,trx_line_description
,product_description
,user_upd_det_factors_flag --Bug 5632300
,defaulting_attribute1 --Bug#6902111
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REQ_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE --event_class_code
,prl.requisition_header_id --trx_id
,PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE --trx_level_type
,prl.requisition_line_id --trx_line_id
,'INVOICE' --line_class
,nvl(prl.tax_attribute_update_code,'UPDATE') --line_level_action
,'ITEM' --trx_line_type
,NVL(prl.need_by_date, sysdate) --trx_line_date
,'N' --line_amt_includes_tax_flag
,nvl(prl.amount, prl.unit_price*prl.quantity) --line_amt
,prl.quantity --trx_line_quantity
,prl.unit_price --unit_price
,prl.item_id --product_id
-- Bug 5335818. Pass in inventory_organization_id
,(SELECT fsp.inventory_organization_id --product_org_id
FROM financials_system_params_all fsp
WHERE fsp.org_id=prl.org_id)
,(SELECT mum.uom_code FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure=prl.unit_meas_lookup_code) --uom_code
,msib.segment1 --product_code
,prl.destination_organization_id --ship_to_party_id
,pv.party_id --ship_from_party_id
,prh.org_id --bill_to_party_id
,pv.party_id --bill_from_party_id
,pvs.party_site_id --ship_from_party_site_id
,pvs.party_site_id --bill_from_party_site_id
,prl.deliver_to_location_id --ship_to_location_id
,(SELECT hzps.location_id --ship_from_location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id = pvs.party_site_id)
,(SELECT location_id FROM hr_all_organization_units --bill_to_location_id
WHERE organization_id=prh.org_id)
,prl.vendor_id --ship_third_pty_acct_id
,prl.vendor_site_id --ship_third_pty_acct_site_id
,null --historical_flag
-- If prl.currency_code is null, insert values corresponding to functional
-- currency for currency_code, rate_date, rate and rate_type
,NVL(prl.currency_code, l_functional_currency_code) --trx_line_currency_code
,NVL2(prl.currency_code, prl.rate_date, sysdate) --trx_line_currency_conv_date
,NVL2(prl.currency_code, prl.rate, l_rate) --trx_line_currency_conv_rate
,NVL2(prl.currency_code, prl.rate_type, l_rate_type) --trx_line_currency_conv_type
,fc.minimum_accountable_unit --trx_line_mau
,NVL(fc.precision, 2) --trx_line_precision
,null --historical_tax_code_id
-- parent_req_line_id is persistent and is not nulled out as in case of
-- PO, so need to insert ATAs(Additional Tax Attributes) here only if the
-- action is create and parent_req_line_id is not null (so that ATAs
-- are populated only for the req line split case)
,DECODE(prl.tax_attribute_update_code, --trx_business_category
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.trx_business_category, null),
null
)
,DECODE(prl.tax_attribute_update_code, --product_category
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.product_category, null),
null
)
,DECODE(prl.tax_attribute_update_code, --product_fisc_classification
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.product_fisc_classification, null),
null
)
,DECODE(prl.tax_attribute_update_code, --line_intended_use
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.line_intended_use, null),
null
)
,DECODE(prl.tax_attribute_update_code, --product_type
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.product_type, null),
null
)
,DECODE(prl.tax_attribute_update_code, --user_defined_fisc_class
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.user_defined_fisc_class, null),
null
)
,DECODE(prl.tax_attribute_update_code, --assessable_value
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.assessable_value, null),
null
)
,DECODE(p_calling_program, --input_tax_classification_code
'REQIMPORT', prl.tax_name,
DECODE(prl.tax_attribute_update_code,
'CREATE', NVL2(prl.parent_req_line_id,
zxldet.input_tax_classification_code, null),
null
)
)
-- Get account id from first distribution, if created
-- otherwise from the expense account of the item
,NVL((SELECT prd.code_combination_id --account_ccid
FROM po_req_distributions_all prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND rownum = 1),
msib.expense_account
)
-- Bug 5025018. Updated tax attribute mappings
,pv.vendor_id --bill_third_pty_acct_id
,pvs.vendor_site_id --bill_third_pty_acct_site_id
-- Bug 5079867. Line number and description in ATI page
,prl.line_num --trx_line_number
,prl.item_description --trx_line_description
,prl.item_description --product_description
--Bug 5632300. Parameter to confirm that tax classification is overridden
,(SELECT 'Y' FROM DUAL --user_upd_det_factors_flag
WHERE p_calling_program = 'REQIMPORT'
AND prl.tax_name IS NOT NULL)
,prl.destination_organization_id --Bug#6902111
FROM po_requisition_headers_all prh, po_requisition_lines_all prl,
zx_lines_det_factors zxldet, po_vendors pv, po_vendor_sites_all pvs,
mtl_system_items_b msib, fnd_currencies fc
WHERE prh.requisition_header_id = p_requisition_header_id
AND prh.requisition_header_id = prl.requisition_header_id
-- Conditions for getting Additional Tax Attributes
-- Do not put a condition on zxldet.trx_id here because that would
-- entail bringing the source_header_id for the shipment being currently
-- processed. Join with trx_line_id itself is unique because the document
-- type has been classified with event_class_code and requisition_line_id
-- will always be unique
AND zxldet.application_id(+) = PO_CONSTANTS_SV.APPLICATION_ID
AND zxldet.entity_code(+) = PO_CONSTANTS_SV.REQ_ENTITY_CODE
AND zxldet.event_class_code(+) = PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE
AND zxldet.trx_level_type(+) = PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE
AND zxldet.trx_line_id(+) = prl.parent_req_line_id
-- Join with vendor tables to get party and party site information
AND pv.vendor_id(+) = prl.vendor_id
AND pvs.vendor_site_id(+) = prl.vendor_site_id
-- Join with items table for item information
AND msib.inventory_item_id(+) = prl.item_id
AND msib.organization_id(+) = prl.org_id
-- Join with fnd_currencies for currency information
AND fc.currency_code(+) = prl.currency_code
-- Conditions that determine that requisition line is 'Active'
AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
AND nvl(prl.cancel_flag, 'N') = 'N'
AND nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND prl.line_location_id IS NULL
AND prl.at_sourcing_flag IS NULL;
INSERT INTO zx_itm_distributions_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_line_id
,trx_level_type
,trx_line_dist_id
,dist_level_action
,trx_line_dist_date
,item_dist_number
,task_id
,award_id
,project_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,trx_line_dist_amt
,trx_line_dist_qty
,trx_line_quantity
,account_ccid
,currency_exchange_rate
,overriding_recovery_rate
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.PO_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE --event_class_code
,pd1.po_header_id --trx_id
,pll.line_location_id --trx_line_id
,PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE --trx_level_type
,pd1.po_distribution_id --trx_line_dist_id
,NVL(pd1.tax_attribute_update_code, 'NO_ACTION') --dist_level_action
,sysdate --trx_line_dist_date
,pd1.distribution_num --item_dist_number
,pd1.task_id --task_id
,pd1.award_id --award_id
,pd1.project_id --project_id
,pd1.expenditure_type --expenditure_type
,pd1.expenditure_organization_id --expenditure_organization_id
,pd1.expenditure_item_date --expenditure_item_date
,DECODE(nvl(pll.matching_basis,pl.matching_basis), --trx_line_dist_amt
'AMOUNT', pd1.amount_ordered,
pd1.quantity_ordered*pll.price_override)
,pd1.quantity_ordered --trx_line_dist_qty
,pll.quantity --trx_line_quantity
,pd1.code_combination_id --account_ccid
,pd1.rate --currency_exchange_rate
, decode(pd1.tax_recovery_override_flag, 'Y', pd1.recovery_rate, null) --overriding_recovery_rate
FROM po_distributions_all pd1, po_line_locations_all pll, po_lines_all pl
WHERE pd1.po_header_id = p_po_header_id_tbl(i)
AND pd1.line_location_id=pll.line_location_id
AND pll.po_line_id=pl.po_line_id
AND (EXISTS(SELECT 'SIBLING DIST WITH TAUC'
FROM po_distributions_all pd2
WHERE pd2.line_location_id = pd1.line_location_id
--AND pd2.po_distribution_id<>pd1.po_distribution_id
AND pd2.tax_attribute_update_code IS NOT NULL
)
--OR pd1.tax_attribute_update_code IS NOT NULL
OR pll.tax_attribute_update_code IS NOT NULL
)
AND pd1.po_header_id IN
(SELECT trx_id FROM zx_trx_headers_gt
WHERE event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE);
INSERT INTO zx_itm_distributions_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_line_id
,trx_level_type
,trx_line_dist_id
,dist_level_action
,trx_line_dist_date
,item_dist_number
,task_id
,award_id
,project_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,trx_line_dist_amt
,trx_line_dist_qty
,trx_line_quantity
,account_ccid
,currency_exchange_rate
,overriding_recovery_rate
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.PO_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE --event_class_code
,pd.po_header_id --trx_id
,pll.line_location_id --trx_line_id
,PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE --trx_level_type
,pd.po_distribution_id --trx_line_dist_id
,'NO_ACTION' --dist_level_action
,sysdate --trx_line_dist_date
,pd.distribution_num --item_dist_number
,pd.task_id --task_id
,pd.award_id --award_id
,pd.project_id --project_id
,pd.expenditure_type --expenditure_type
,pd.expenditure_organization_id --expenditure_organization_id
,pd.expenditure_item_date --expenditure_item_date
-- Bug 5202059. Pass in correct amount
,DECODE(nvl(pll.matching_basis,pl.matching_basis), --trx_line_dist_amt
'AMOUNT', pd.amount_ordered,
pd.quantity_ordered*pll.price_override)
,pd.quantity_ordered --trx_line_dist_qty
,pll.quantity --trx_line_quantity
,pd.code_combination_id --account_ccid
,pd.rate --currency_exchange_rate
, decode(pd.tax_recovery_override_flag, 'Y', pd.recovery_rate, null) --overriding_recovery_rate
FROM po_distributions_all pd, po_line_locations_all pll, po_lines_all pl
WHERE pd.po_header_id = p_po_header_id
AND pd.line_location_id=pll.line_location_id
AND pll.po_line_id = pl.po_line_id
-- Conditions that determine that po line is 'Active'
AND nvl(pl.cancel_flag,'N') = 'N'
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pll.cancel_flag,'N') = 'N'
AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED';
INSERT INTO zx_itm_distributions_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_line_id
,trx_level_type
,trx_line_dist_id
,dist_level_action
,trx_line_dist_date
,item_dist_number
,task_id
,award_id
,project_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,trx_line_dist_amt
,trx_line_dist_qty
,trx_line_quantity
,account_ccid
,currency_exchange_rate
,overriding_recovery_rate
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REL_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE --event_class_code
,pd1.po_release_id --trx_id
,pll.line_location_id --trx_line_id
,PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE --trx_level_type
,pd1.po_distribution_id --trx_line_dist_id
,nvl(pd1.tax_attribute_update_code, 'NO_ACTION') --dist_level_action
,sysdate --trx_line_dist_date
,pd1.distribution_num --item_dist_number
,pd1.task_id --task_id
,pd1.award_id --award_id
,pd1.project_id --project_id
,pd1.expenditure_type --expenditure_type
,pd1.expenditure_organization_id --expenditure_organization_id
,pd1.expenditure_item_date --expenditure_item_date
,DECODE(pl.matching_basis, --trx_line_dist_amt
'AMOUNT', pd1.amount_ordered,
pd1.quantity_ordered*pll.price_override)
,pd1.quantity_ordered --trx_line_dist_qty
,pll.quantity --trx_line_quantity
,pd1.code_combination_id --account_ccid
,pd1.rate --currency_exchange_rate
, decode(pd1.tax_recovery_override_flag, 'Y', pd1.recovery_rate, null) --overriding_recovery_rate
FROM po_distributions_all pd1, po_line_locations_all pll, po_lines_all pl
WHERE pd1.po_release_id = p_po_release_id_tbl(i)
AND pd1.line_location_id=pll.line_location_id
AND pll.po_line_id=pl.po_line_id
AND (EXISTS(SELECT 'SIBLING DIST WITH TAUC'
FROM po_distributions_all pd2
WHERE pd2.line_location_id = pd1.line_location_id
--AND pd2.po_distribution_id<>pd1.po_distribution_id
AND pd2.tax_attribute_update_code IS NOT NULL
)
--OR pd1.tax_attribute_update_code IS NOT NULL
OR pll.tax_attribute_update_code IS NOT NULL
)
AND pd1.po_release_id IN
(SELECT trx_id FROM zx_trx_headers_gt
WHERE event_class_code = PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE);
INSERT INTO zx_itm_distributions_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_line_id
,trx_level_type
,trx_line_dist_id
,dist_level_action
,trx_line_dist_date
,item_dist_number
,task_id
,award_id
,project_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,trx_line_dist_amt
,trx_line_dist_qty
,trx_line_quantity
,account_ccid
,currency_exchange_rate
,overriding_recovery_rate
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REL_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE --event_class_code
,pd.po_release_id --trx_id
,pll.line_location_id --trx_line_id
,PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE --trx_level_type
,pd.po_distribution_id --trx_line_dist_id
,'NO_ACTION' --dist_level_action
,sysdate --trx_line_dist_date
,pd.distribution_num --item_dist_number
,pd.task_id --task_id
,pd.award_id --award_id
,pd.project_id --project_id
,pd.expenditure_type --expenditure_type
,pd.expenditure_organization_id --expenditure_organization_id
,pd.expenditure_item_date --expenditure_item_date
-- Bug 5202059. Pass in correct amount
,DECODE(pl.matching_basis, --trx_line_dist_amt
'AMOUNT', pd.amount_ordered,
pd.quantity_ordered*pll.price_override)
,pd.quantity_ordered --trx_line_dist_qty
,pll.quantity --trx_line_quantity
,pd.code_combination_id --account_ccid
,pd.rate --currency_exchange_rate
, decode(pd.tax_recovery_override_flag, 'Y', pd.recovery_rate, null) --overriding_recovery_rate
FROM po_distributions_all pd, po_line_locations_all pll, po_lines_all pl
WHERE pd.po_release_id = p_po_release_id
AND pd.line_location_id = pll.line_location_id
AND pll.po_line_id = pl.po_line_id
-- Conditions that determine that po line is 'Active'
AND nvl(pl.cancel_flag,'N') = 'N'
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pll.cancel_flag,'N') = 'N'
AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED';
INSERT INTO zx_itm_distributions_gt(
application_id
,entity_code
,event_class_code
,trx_id
,trx_line_id
,trx_level_type
,trx_line_dist_id
,dist_level_action
,trx_line_dist_date
,item_dist_number
,dist_intended_use
,task_id
,award_id
,project_id
,expenditure_type
,expenditure_organization_id
,expenditure_item_date
,trx_line_dist_amt
,trx_line_dist_qty
,trx_line_quantity
,account_ccid
,historical_flag
,overriding_recovery_rate)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID --application_id
,PO_CONSTANTS_SV.REQ_ENTITY_CODE --entity_code
,PO_CONSTANTS_SV.REQ_EVENT_CLASS_CODE --event_class_code
,prl.requisition_header_id --trx_id
,prl.requisition_line_id --trx_line_id
,PO_CONSTANTS_SV.REQ_TRX_LEVEL_TYPE --trx_level_type
,prd.distribution_id --trx_line_dist_id
,'CREATE' --dist_level_action
,sysdate --trx_line_dist_date
,prd.distribution_num --item_dist_number
,null --dist_intended_use
,prd.task_id --task_id
,prd.award_id --award_id
,prd.project_id --project_id
,prd.expenditure_type --expenditure_type
,prd.expenditure_organization_id --expenditure_organization_id
,prd.expenditure_item_date --expenditure_item_date
,DECODE(prl.matching_basis,'AMOUNT', prd.REQ_LINE_AMOUNT, prd.req_line_quantity*prl.unit_price) --trx_line_dist_amt
,prd.req_line_quantity --trx_line_dist_qty
,prl.quantity --trx_line_quantity
,prd.code_combination_id --account_ccid
,null --historical_flag
,decode(prd.tax_recovery_override_flag, 'Y', prd.recovery_rate, null) --overriding_recovery_rate
FROM po_requisition_lines_all prl, po_req_distributions_all prd
WHERE prl.requisition_header_id = p_requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
-- Conditions that determine that requisition line is 'Active'
AND prl.SOURCE_TYPE_CODE<>'INVENTORY'
AND nvl(prl.modified_by_agent_flag, 'N') = 'N'
AND nvl(prl.cancel_flag, 'N') = 'N'
AND nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND prl.line_location_id IS NULL
AND prl.at_sourcing_flag IS NULL;
SELECT 'Y' INTO l_result
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM po_headers_all POH,
po_lines_all POL,
po_line_locations_all PLL,
po_distributions_all POD
WHERE POH.po_header_id = p_po_header_id
AND POH.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND PLL.line_location_id = POD.line_location_id
AND NVL(POL.cancel_flag,'N') = 'N'
AND NVL(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND NVL(PLL.cancel_flag,'N') = 'N'
AND NVL(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND (POH.tax_attribute_update_code IS NOT NULL OR
POL.tax_attribute_update_code IS NOT NULL OR
PLL.tax_attribute_update_code IS NOT NULL OR
POD.tax_attribute_update_code IS NOT NULL
)
);
SELECT 'Y' INTO l_Result
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM po_releases_all POR,
po_line_locations_all PLL,
po_distributions_all POD
WHERE POR.po_release_id = p_po_release_id
AND POR.po_release_id = PLL.po_release_id
AND PLL.line_location_id = POD.line_location_id
AND NVL(PLL.cancel_flag,'N') = 'N'
AND NVL(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND (POR.tax_attribute_update_code IS NOT NULL OR
PLL.tax_attribute_update_code IS NOT NULL OR
POD.tax_attribute_update_code IS NOT NULL
)
);
SELECT 'Y' INTO l_Result
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM po_requisition_headers_all PRH,
po_requisition_lines_all PRL
WHERE PRH.requisition_header_id = p_req_header_id
AND PRH.requisition_header_id = PRL.requisition_header_id
AND NVL(PRL.cancel_flag, 'N') = 'N'
AND NVL(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND (PRH.tax_attribute_update_code IS NOT NULL OR
PRL.tax_attribute_update_code IS NOT NULL
)
);
SELECT
zxhgt.internal_organization_id
,zxhgt.application_id
,zxhgt.entity_code
,zxhgt.event_class_code
,zxhgt.event_type_code
,zxhgt.trx_id
,zxhgt.trx_date
,zxhgt.ledger_id
,zxhgt.legal_entity_id
,zxhgt.rounding_bill_to_party_id
,zxhgt.quote_flag
,zxhgt.document_sub_type
,zxhgt.default_taxation_country
,zxlgt.trx_level_type
,zxlgt.trx_line_id
,zxlgt.line_class
,zxlgt.line_level_action
,zxlgt.trx_line_type
,zxlgt.trx_line_date
,zxlgt.trx_business_category
,zxlgt.line_intended_use
,zxlgt.user_defined_fisc_class
,zxlgt.line_amt_includes_tax_flag
,zxlgt.line_amt
,zxlgt.trx_line_quantity
,zxlgt.unit_price
,zxlgt.product_id
,zxlgt.product_fisc_classification
,zxlgt.product_org_id
,zxlgt.uom_code
,zxlgt.product_type
,zxlgt.product_code
,zxlgt.product_category
,zxlgt.ship_to_party_id
,zxlgt.ship_from_party_id
,zxlgt.bill_to_party_id
,zxlgt.bill_from_party_id
,zxlgt.ship_from_party_site_id
,zxlgt.bill_from_party_site_id
,zxlgt.ship_to_location_id
,ship_from_location_id
,zxlgt.bill_to_location_id
,zxlgt.ship_third_pty_acct_id
,zxlgt.ship_third_pty_acct_site_id
,zxlgt.assessable_value
,zxlgt.historical_flag
,zxlgt.trx_line_currency_code
,zxlgt.trx_line_currency_conv_date
,zxlgt.trx_line_currency_conv_rate
,zxlgt.trx_line_currency_conv_type
,zxlgt.trx_line_mau
,zxlgt.trx_line_precision
,zxlgt.historical_tax_code_id
,zxlgt.input_tax_classification_code
,zxlgt.account_ccid
-- Bug 5025018. Updated tax attribute mappings
,zxlgt.bill_third_pty_acct_id
,zxlgt.bill_third_pty_acct_site_id
-- Bug 5079867. Line number and description in ATI page
,zxlgt.trx_line_number
,zxlgt.trx_line_description
,zxlgt.product_description
-- Bug 5082762. Product Type dropped when line is updated
,zxlgt.user_upd_det_factors_flag
,zxlgt.DEFAULTING_ATTRIBUTE1 --Bug#6902111
INTO
ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.internal_organization_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.application_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.entity_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_class_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_type_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_date(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ledger_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.legal_entity_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.rounding_bill_to_party_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.quote_flag(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.document_sub_type(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.default_taxation_country(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_level_type(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_class(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_level_action(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_type(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_date(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_business_category(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_intended_use(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.user_defined_fisc_class(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_amt_includes_tax_flag(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_amt(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_quantity(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.unit_price(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_fisc_classification(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.uom_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_type(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_category(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_party_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_party_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_from_party_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_site_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_from_party_site_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_location_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_location_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_location_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_third_pty_acct_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_third_pty_acct_site_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.assessable_value(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.historical_flag(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_currency_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_currency_conv_date(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_currency_conv_rate(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_currency_conv_type(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_mau(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_precision(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.historical_tax_code_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.input_tax_classification_code(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.account_ccid(l_count)
-- Bug 5025018. Updated tax attribute mappings
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_third_pty_acct_id(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_third_pty_acct_site_id(l_count)
-- Bug 5079867. Line number and description in ATI page
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_number(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_description(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_description(l_count)
-- Bug 5082762. Product Type dropped when line is updated
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.user_upd_det_factors_flag(l_count)
,ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.DEFAULTING_ATTRIBUTE1(l_count) --Bug#6902111
FROM zx_trx_headers_gt zxhgt, zx_transaction_lines_gt zxlgt
WHERE zxlgt.trx_line_id = i.trx_line_id
AND zxlgt.trx_id = zxhgt.trx_id;
PROCEDURE SHIPMENT_DIST_DELETED_FROM_OA
(
P_PO_HEADER_ID IN NUMBER,
P_DEL_SHIPMENT_TABLE IN PO_TBL_NUMBER,
P_DEL_DIST_SHIPMENT_TABLE IN PO_TBL_NUMBER
)
IS
l_module_name CONSTANT VARCHAR2(100) := 'shipment_dist_deleted_from_oa';
UPDATE po_line_locations_all
SET tax_attribute_update_code = NVL(tax_attribute_update_code,'DIST_DELETE')
WHERE line_location_id = p_del_dist_shipment_table(i);
INSERT INTO zx_transaction_lines_gt
(
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
line_amt,
line_amt_includes_tax_flag
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID, --application_id
PO_CONSTANTS_SV.PO_ENTITY_CODE, --entity_code
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE, --event_class_code
p_po_header_id, --trx_id
PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE, --trx_level_type
p_del_shipment_table(i), --trx_line_id
'DELETE', --line_level_action
-1,
'N'
-- Added the EXISTS clause so that only one row is inserted
--into gt table even if there are multiple tax lines for each shipment
FROM dual
WHERE EXISTS
(SELECT 'TAX LINES EXIST'
FROM zx_lines zl
-- Restrict to only rows that have corresponding tax lines
-- Since this is called from OA, so conditions are only for SPO
WHERE zl.trx_id = p_po_header_id
AND zl.trx_line_id = p_del_shipment_table(i)
AND zl.application_id = PO_CONSTANTS_SV.APPLICATION_ID
AND zl.entity_code = PO_CONSTANTS_SV.PO_ENTITY_CODE
AND zl.event_class_code = PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE
AND zl.trx_level_type = PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE
);
END SHIPMENT_DIST_DELETED_FROM_OA;
SELECT 'Y'
INTO l_clear_tables
FROM dual
WHERE EXISTS
(SELECT 'CLEAR'
FROM zx_transaction_lines_gt zxlgt
WHERE zxlgt.line_level_action <> 'DELETE');
DELETE ZX_TRX_HEADERS_GT;
DELETE ZX_TRANSACTION_LINES_GT;
DELETE ZX_ITM_DISTRIBUTIONS_GT;
DELETE ZX_VALIDATION_ERRORS_GT;
DELETE ZX_ERRORS_GT;
FUNCTION any_tax_attributes_updated(
p_doc_type IN VARCHAR2,
p_doc_level IN VARCHAR2,
p_doc_level_id IN NUMBER,
p_trx_currency IN VARCHAR2 DEFAULT NULL,
p_rate_type IN VARCHAR2 DEFAULT NULL,
p_rate_date IN DATE DEFAULT NULL,
p_rate IN NUMBER DEFAULT NULL,
p_fob IN VARCHAR2 DEFAULT NULL,
p_vendor_id IN NUMBER DEFAULT NULL,
p_vendor_site_id IN NUMBER DEFAULT NULL,
p_bill_to_loc IN NUMBER DEFAULT NULL, --
p_uom IN VARCHAR2 DEFAULT NULL,
p_price IN NUMBER DEFAULT NULL,
p_qty IN NUMBER DEFAULT NULL,
p_price_override IN NUMBER DEFAULT NULL, --
p_amt IN NUMBER DEFAULT NULL,
p_ship_to_org IN NUMBER DEFAULT NULL,
p_ship_to_loc IN NUMBER DEFAULT NULL,
p_need_by_date IN DATE DEFAULT NULL,
p_src_doc IN NUMBER DEFAULT NULL,
p_src_ship IN NUMBER DEFAULT NULL,
p_ccid IN NUMBER DEFAULT NULL,
p_tax_rec_rate IN NUMBER DEFAULT NULL,
p_project IN NUMBER DEFAULT NULL,
p_task IN NUMBER DEFAULT NULL,
p_award IN NUMBER DEFAULT NULL,
p_exp_type IN VARCHAR2 DEFAULT NULL,
p_exp_org IN NUMBER DEFAULT NULL,
p_exp_date IN DATE DEFAULT NULL,
p_dist_quantity_ordered IN NUMBER DEFAULT NULL,
p_dist_amount_ordered IN NUMBER DEFAULT NULL
) RETURN BOOLEAN IS
pragma AUTONOMOUS_TRANSACTION;
l_module_name CONSTANT VARCHAR2(100) := 'ANY_TAX_ATTRIBUTES_UPDATED';
SELECT currency_code,
rate_type,
rate_date,
rate,
fob_lookup_code,
vendor_id,
vendor_site_id,
bill_to_location_id --
INTO l_trx_currency,
l_rate_type,
l_rate_date,
l_rate,
l_fob,
l_vendor_id,
l_vendor_site_id,
l_bill_to_loc --
FROM po_headers_all
WHERE po_header_id = p_doc_level_id;
SELECT unit_meas_lookup_code,
unit_price
INTO l_uom,
l_price
FROM po_lines_all
WHERE po_line_id =p_doc_level_id;
SELECT quantity,
price_override, --
amount,
ship_to_organization_id,
ship_to_location_id,
need_by_date
INTO l_qty,
l_price_override, --
l_amt,
l_ship_to_org,
l_ship_to_loc,
l_need_by_date
FROM po_line_locations_all
WHERE line_location_id = p_doc_level_id;
SELECT CODE_COMBINATION_ID,
RECOVERY_RATE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
QUANTITY_ORDERED,
AMOUNT_ORDERED
INTO l_ccid,
l_tax_rec_rate,
l_project,
l_task ,
l_award,
l_exp_type,
l_exp_org,
l_exp_date,
l_dist_quantity_ordered,
l_dist_amount_ordered
FROM po_distributions_all
WHERE po_distribution_id =p_doc_level_id;
SELECT currency_code,
rate_type,
rate_date,
rate,
fob_lookup_code,
vendor_id,
vendor_site_id
INTO l_trx_currency,
l_rate_type,
l_rate_date,
l_rate,
l_fob,
l_vendor_id,
l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_doc_level_id;
END any_tax_attributes_updated;
SELECT *
BULK COLLECT INTO l_zx_headers_tbl
FROM zx_trx_headers_gt;
SELECT *
BULK COLLECT INTO l_zx_lines_tbl
FROM zx_transaction_lines_gt;
SELECT *
BULK COLLECT INTO l_zx_dists_tbl
FROM zx_itm_distributions_gt;
l_header_tauc PO_HEADERS_ALL.tax_attribute_update_code%TYPE;
SELECT ph.tax_attribute_update_code
INTO l_header_tauc
FROM po_headers_all ph
WHERE ph.po_header_id = p_po_header_id_tbl(i);
SELECT pl.po_line_id, pl.tax_attribute_update_code
BULK COLLECT INTO l_line_id_tbl, l_line_tauc_tbl
FROM po_lines_all pl
WHERE pl.po_header_id = p_po_header_id_tbl(i);
SELECT pll.line_location_id, pll.tax_attribute_update_code
BULK COLLECT INTO l_ship_id_tbl, l_ship_tauc_tbl
FROM po_line_locations_all pll
WHERE pll.po_line_id = l_line_id_tbl(j);
SELECT pd.po_distribution_id, pd.tax_attribute_update_code
BULK COLLECT INTO l_dist_id_tbl, l_dist_tauc_tbl
FROM po_distributions_all pd
WHERE pd.line_location_id = l_ship_id_tbl(k);
l_header_tauc PO_RELEASES_ALL.tax_attribute_update_code%TYPE;
SELECT pr.tax_attribute_update_code
INTO l_header_tauc
FROM po_releases_all pr
WHERE pr.po_release_id = p_po_release_id_tbl(i);
SELECT pll.line_location_id, pll.tax_attribute_update_code
BULK COLLECT INTO l_ship_id_tbl, l_ship_tauc_tbl
FROM po_line_locations_all pll
WHERE pll.po_release_id = p_po_release_id_tbl(i);
SELECT pd.po_distribution_id, pd.tax_attribute_update_code
BULK COLLECT INTO l_dist_id_tbl, l_dist_tauc_tbl
FROM po_distributions_all pd
WHERE pd.line_location_id = l_ship_id_tbl(j);
l_header_tauc PO_REQUISITION_HEADERS_ALL.tax_attribute_update_code%TYPE;
SELECT prh.tax_attribute_update_code
INTO l_header_tauc
FROM po_requisition_headers_all prh
WHERE prh.requisition_header_id = p_requisition_header_id;
SELECT prl.requisition_line_id, prl.tax_attribute_update_code
BULK COLLECT INTO l_line_id_tbl, l_line_tauc_tbl
FROM po_requisition_lines_all prl
WHERE prl.requisition_header_id = p_requisition_header_id;
INSERT INTO zx_trx_headers_gt
(
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
legal_entity_id,
rounding_ship_from_party_id
)
SELECT
ph.org_id,
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.PO_ENTITY_CODE,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
PO_CONSTANTS_SV.PO_ADJUSTED,
ph.po_header_id,
sysdate, -- dummy value
PO_CORE_S.get_default_legal_entity_id(ph.org_id),
(SELECT party_id FROM po_vendors --rounding_ship_from_party_id
WHERE vendor_id=ph.vendor_id)
FROM po_headers_all ph
WHERE ph.po_header_id = p_document_id;
INSERT INTO zx_transaction_lines_gt
(
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
line_amt_includes_tax_flag,
line_amt
)
VALUES
(
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.PO_ENTITY_CODE,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
p_document_id,
PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE,
p_shipment_id,
'CANCEL',
'N', -- dummy value
0 -- dummy value
);
INSERT INTO zx_trx_headers_gt
(
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
legal_entity_id,
rounding_ship_from_party_id
)
SELECT
ph.org_id,
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.PO_ENTITY_CODE,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
PO_CONSTANTS_SV.PO_ADJUSTED,
ph.po_header_id,
sysdate, -- dummy value
PO_CORE_S.get_default_legal_entity_id(ph.org_id),
(SELECT party_id FROM po_vendors --rounding_ship_from_party_id
WHERE vendor_id=ph.vendor_id)
FROM po_headers_all ph
WHERE ph.po_header_id = p_document_id;
INSERT INTO zx_transaction_lines_gt
(
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
line_amt_includes_tax_flag,
line_amt
)
SELECT
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.PO_ENTITY_CODE,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
pll.po_header_id,
PO_CONSTANTS_SV.PO_TRX_LEVEL_TYPE,
pll.line_location_id,
'CANCEL', --line_level_action
'N', -- dummy value
0 -- dummy value
FROM po_line_locations_all pll
WHERE pll.po_line_id = p_line_id;
SELECT ph.org_id
INTO l_org_id
FROM po_headers_all ph
WHERE ph.po_header_id = p_document_id;
PO_TAX_INTERFACE_PVT.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_org_id => l_org_id,
p_document_type => PO_CONSTANTS_SV.PO,
p_document_id => p_document_id,
p_event_type_code => PO_CONSTANTS_SV.PO_CANCELLED);
PO_LOG.stmt(d_module_base,d_progress,'After Calling PO_TAX_INTERFACE_PVT.global_document_update');
INSERT INTO zx_trx_headers_gt
(
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
legal_entity_id,
rounding_ship_from_party_id
)
SELECT
pr.org_id, -- dummy value
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.REL_ENTITY_CODE,
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE,
PO_CONSTANTS_SV.REL_ADJUSTED,
pr.po_release_id,
sysdate, -- dummy value
PO_CORE_S.get_default_legal_entity_id(pr.org_id),
(SELECT party_id FROM po_vendors --rounding_ship_from_party_id
WHERE vendor_id=ph.vendor_id)
FROM po_releases_all pr, po_headers_all ph
WHERE pr.po_release_id = p_document_id
AND pr.po_header_id = ph.po_header_id;
INSERT INTO zx_transaction_lines_gt
(
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
line_amt_includes_tax_flag,
line_amt
)
VALUES
(
PO_CONSTANTS_SV.APPLICATION_ID,
PO_CONSTANTS_SV.REL_ENTITY_CODE,
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE,
p_document_id,
PO_CONSTANTS_SV.REL_TRX_LEVEL_TYPE,
p_shipment_id,
'CANCEL',
'N', -- dummy value
0 -- dummy value
);
SELECT pr.org_id
INTO l_org_id
FROM po_releases_all pr
WHERE pr.po_release_id = p_document_id;
PO_TAX_INTERFACE_PVT.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_org_id => l_org_id,
p_document_type => PO_CONSTANTS_SV.RELEASE,
p_document_id => p_document_id,
p_event_type_code => PO_CONSTANTS_SV.REL_CANCELLED);
PO_LOG.stmt(d_module_base,d_progress,'After Calling PO_TAX_INTERFACE_PVT.global_document_update');
PROCEDURE global_document_update(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_org_id IN NUMBER,
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_event_type_code IN VARCHAR2
) IS
l_module_name CONSTANT VARCHAR2(100) := 'GLOBAL_DOCUMENT_UPDATE';
PO_LOG.stmt(d_module_base,d_progress,'Before Calling ZX_API_PUB.global_document_update');
ZX_API_PUB.global_document_update(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_rec => l_trx_rec);
PO_LOG.exc(d_module_base, d_progress, 'Unhandled exception in procedure global_document_update');
END global_document_update;
SELECT ph.authorization_status
INTO l_authorization_status
FROM po_headers_all ph
WHERE ph.po_header_id = p_document_id;
SELECT pr.authorization_status
INTO l_authorization_status
FROM po_releases_all pr
WHERE pr.po_release_id = p_document_id;
UPDATE po_headers_all ph
SET ph.authorization_status = PO_CONSTANTS_SV.IN_PROCESS,
ph.approved_flag = 'N'
WHERE ph.po_header_id = p_document_id;
UPDATE po_releases_all pr
SET pr.authorization_status = PO_CONSTANTS_SV.IN_PROCESS,
pr.approved_flag = 'N'
WHERE pr.po_release_id = p_document_id;
UPDATE po_headers_all ph
SET ph.authorization_status = PO_CONSTANTS_SV.REQUIRES_REAPPROVAL,
ph.approved_flag = 'R'
WHERE ph.po_header_id = p_document_id;
UPDATE po_releases_all pr
SET pr.authorization_status = PO_CONSTANTS_SV.REQUIRES_REAPPROVAL,
pr.approved_flag = 'R'
WHERE pr.po_release_id = p_document_id;
UPDATE po_line_locations_all pll
SET pll.approved_flag = 'R'
WHERE pll.line_location_id = p_line_location_id_tbl(i)
AND pll.approved_flag = 'Y';