[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(Select distinct po_line_id
from po_distributions_all
where po_header_id = p_po_header_id
and
( ( p_called_from = lv_called_release and po_release_id = p_po_release_id) --'RELEASE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
OR
( p_called_from = lv_called_po) --'PO'
)
)
Loop
ja_in_57f4_lines_insert
(
p_po_header_id ,
c_Line_rec.po_line_id ,
p_po_release_id ,
p_vendor_id ,
p_vendor_site_id ,
p_called_from
);
PROCEDURE ja_in_57f4_lines_insert
(p_po_header_id po_headers_all.po_header_id%type ,
p_po_line_id po_lines_all.po_line_id%type ,
p_po_release_id po_releases_all.po_release_id%type,
p_vendor_id po_vendors.vendor_id%type ,
p_vendor_site_id po_vendor_sites_all.vendor_site_id%type,
p_called_from varchar2
)
IS
cursor c_check_osp_po_distrib(cp_line_type_id number) is
select outside_operation_flag
from po_line_types_b
where line_type_id = cp_line_type_id ;
select 1
from JAI_PO_OSP_HDRS hdr , JAI_PO_OSP_LINES lines
where hdr.form_id = lines.form_id
AND hdr.po_header_id = p_po_header_id
AND (
( p_called_from = lv_called_po --'PO' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND lines.po_line_id = p_po_line_id /* This condition added by ssumaith - bug# 4218628 */
)
OR ( hdr.oth_doc_id = p_po_release_id
and p_Called_from = lv_called_release --'RELEASE'
AND lines.po_line_id = p_po_line_id /* This condition added by ssumaith - bug# 4218628 */
)
)
AND nvl(cancel_flag,'N') = 'N' ;/*5699863 - csahoo */
SELECT excise_flag, item_tariff
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id;
SELECT JAI_PO_OSP_HDRS_S.NEXTVAL
FROM dual;
SELECT JAI_PO_OSP_LINES_S.NEXTVAL
FROM dual;
SELECT osp_return_days, osp_excise_percent
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_org_id;
SELECT count(*)
FROM JAI_PO_OSP_HDRS
WHERE ( po_header_id = p_po_header_id and p_called_from = 'PO')
OR ( oth_doc_id = p_po_release_id and p_called_from = 'RELEASE')
AND organization_id = cp_org_id
AND location_id = cp_loc_id
AND nvl(cancel_flag,'N') = 'N';/*5699863*/
SELECT form_id
FROM JAI_PO_OSP_HDRS
WHERE ( po_header_id = p_po_header_id and p_called_from = 'PO')
OR ( oth_doc_id = p_po_release_id and p_called_from = 'RELEASE')
AND organization_id = cp_org_id
AND location_id = cp_loc_id;
SELECT *
FROM mtl_system_items
WHERE organization_id = cp_org_id
AND inventory_item_id = cp_item_id;
SELECT pll.operand
FROM qp_list_lines_v pll,
JAI_CMN_VENDOR_SITES jvs
WHERE pll.list_header_id = jvs.price_list_id
AND pll.product_attr_value = to_char(cp_item_id)
and product_attribute_context = 'ITEM'
AND jvs.vendor_id = cp_vendor
AND jvs.vendor_site_id = cp_vendor_site
AND pll.product_uom_Code = cp_uom_code
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT pll.operand
FROM qp_list_lines_v pll,
JAI_CMN_VENDOR_SITES jvs
WHERE pll.list_header_id = jvs.price_list_id
AND pll.product_attr_value = to_char(cp_item_id)
AND product_attribute_context = 'ITEM'
AND jvs.vendor_id = cp_vendor
and jvs.vendor_site_id = 0
AND pll.product_uom_Code = cp_uom_code
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT list_price_per_unit
FROM mtl_system_items
WHERE inventory_item_id = cp_item_id AND organization_id = cp_org_id;
SELECT cic.item_cost
FROM CST_ITEM_COSTS CIC,
CST_COST_TYPES CCT
WHERE cic.cost_type_id = cct.cost_type_id
AND cic.inventory_item_id = cp_item_id
AND cic.organization_id = cp_org_id
AND cct.allow_updates_flag = 2;
select assembly_item_id
from bom_bill_of_materials
where bill_sequence_id in
(
select bill_sequence_id
from bom_inventory_components
where component_Sequence_id = cp_component_sequence_id
);
select primary_item_id
from wip_discrete_jobs
where wip_entity_id = cp_wip_entity_id;
SELECT po_line_id , item_id, unit_meas_lookup_code,
unit_price , line_type_id
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT ship_to_location_id
FROM po_line_locations_all
WHERE po_header_id = p_po_header_id
AND po_line_id = p_po_line_id;
SELECT common_routing_sequence_id
FROM wip_discrete_jobs
WHERE wip_entity_id = cp_wip_entity;
SELECT COUNT(1)
FROM bom_operation_sequences
WHERE routing_sequence_ID = cp_routing_seq_id
AND operation_seq_num < cp_wip_operation ;
SELECT count(1)
FROM wip_requirement_operations
WHERE wip_entity_id = cp_wip_entity_id
AND operation_seq_num = cp_wip_op_seq_num
AND wip_supply_type <> 6;
SELECT COUNT(1)
FROM JAI_PO_OSP_ITM_DTLS dtl
WHERE osp_item_id = cp_item_id;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.ja_in_57f4_lines_insert';
(SELECT SUM(quantity_ordered) quantity_ordered,
destination_organization_id orgn_id ,
deliver_to_location_id loc_id ,
wip_entity_id, wip_operation_seq_num,
wip_repetitive_schedule_id, wip_line_id
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
and po_line_id = rec_po_line_cur.po_line_id
and
( ( p_called_from = 'RELEASE' and po_release_id = p_po_release_id)
OR
( p_called_from = 'PO' )
)
GROUP BY destination_organization_id,deliver_to_location_id,
wip_entity_id, wip_operation_seq_num,
wip_repetitive_schedule_id, wip_line_id
)
Loop
open c_location_id;
insert into JAI_PO_OSP_HDRS
*/
if ln_header_ins_flag = 1 then
lv_src_release := 'RELEASE' ;
INSERT INTO JAI_PO_OSP_HDRS (
FORM_ID,
PO_HEADER_ID,
VENDOR_ID,
VENDOR_SITE_ID,
PROCESS_TIME,
ORGANIZATION_ID,
LOCATION_ID,
SOURCE,
ISSUE_APPROVED,
RECEIPT_APPROVED,
CANCEL_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OTH_DOC_ID,
PRIMARY_FLAG)
VALUES
(
ln_form_id ,
p_po_header_id ,
ln_vendor ,
ln_vendor_site ,
ln_osp_return_days ,
c_po_dist.orgn_id ,
nvl(c_po_dist.loc_id,ln_location_id) ,
decode(p_called_from , lv_src_release ,lv_src_po_release,lv_src_po,lv_src_pur_ord), /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
'N',
'N',
'N',
sysdate,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id,
p_po_release_id,
'Y'
);
( SELECT required_quantity ,quantity_per_assembly,inventory_item_id ,
component_sequence_id , 'W' item_type, comments
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = c_po_dist.wip_entity_id
AND wro.operation_seq_num = c_po_dist.wip_operation_seq_num
AND wip_supply_type <> 6
UNION
select c_po_dist.quantity_ordered , 1 , rec_po_line_cur.item_id , 1 ,
'P' item_type, 'PO Entry' comments
from dual
where ( ln_routing_ctr > 0 or ln_routing_seq_id is NULL OR ln_ja_ctr = 0) /*Bug 4609260*/
union
select 1 , dtl.quantity , dtl.item_id , 1 , 'M' item_type,'Manual Entry' comments
from JAI_PO_OSP_ITM_DTLS dtl
where osp_item_id = rec_po_line_cur.item_id
) /*
The wip_supply_type != 6 indicates that phantom kit itself should not come in the 57F4 challan instead the
components should feature in the 57F4. Supply Type = 6 indicates a phantom supply type.
The query after the first union takes care of the scenario where the PO item needs to be part of the 57F4 if it
is not the first operation.
The query after the second union takes care of the manual BOM setup by the user.
*/
Loop
ln_line_id := 0;
INSERT INTO JAI_PO_OSP_LINES (
FORM_ID ,
LINE_ID ,
PO_LINE_ID ,
PO_DISTRIBUTION_ID ,
ITEM_ID ,
WIP_ENTITY_ID ,
WIP_LINE_ID ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_OPERATION_SEQUENCE_NUM ,
ASSEMBLY_ID ,
DESPATCH_QTY ,
ITEM_UOM ,
ITEM_VALUE ,
TARIFF_CODE ,
EXCISE_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
PARENT_ITEM_ID ,
COMP_QTY_PA ,
BAL_PARENT_ITEM_QTY ,
SOURCE_CODE ,
ORIGINAL_QTY,
PROCESS_REQD)
VALUES (
ln_form_id ,
ln_line_id ,
rec_po_line_cur.po_line_id ,
NULL ,
c_component_rec.inventory_item_id ,
c_po_dist.wip_entity_id ,
c_po_dist.wip_line_id ,
c_po_dist.wip_repetitive_schedule_id ,
c_po_dist.wip_operation_seq_num ,
ln_assembly_id ,
-- Bug 7028169. Added by Lakshmi Gopalsami
ln_despatch_quantity ,
r_get_comp_item_details.primary_uom_code ,
ln_item_Value ,
lv_tariff_code ,
nvl(ln_osp_excise_percent,0) ,
sysdate ,
fnd_global.user_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
ln_parent_item_id ,
c_component_rec.quantity_per_assembly,
ln_bal_parent_item_qty ,
lv_source_code ,
-- Bug 7028169. Added by Lakshmi Gopalsami
ln_original_quantity,
c_component_rec.comments
);
end ja_in_57f4_lines_insert;
select hdr.primary_flag, hdr.primary_form_id, dtl.despatch_qty, dtl.po_line_id, dtl.item_id
from JAI_PO_OSP_HDRS hdr, JAI_PO_OSP_LINES dtl
where hdr.form_id = dtl.form_id
and hdr.form_id = p_form_id;
UPDATE JAI_PO_OSP_HDRS
SET cancel_flag = 'Y'
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login= fnd_global.login_id
WHERE form_id = p_form_id
OR (primary_form_id = p_form_id
AND source <> lv_rtv_source
);
UPDATE JAI_PO_OSP_LINES set dispatched_qty = dispatched_qty - i_rec.despatch_qty
where form_id = i_rec.primary_form_id
and item_id = i_rec.item_id
and po_line_id = i_rec.po_line_id;
SELECT shipment_header_id, shipment_line_id, transaction_type, organization_id, location_id,
quantity, unit_of_measure, subinventory, vendor_id, vendor_site_id,
source_document_code, po_header_id, po_line_id, po_line_location_id, po_release_id
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id;
SELECT item_id, ship_to_location_id
FROM rcv_shipment_lines
WHERE shipment_line_id = r_base_trx.shipment_line_id;
SELECT excise_flag, item_tariff
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = v_item_id
AND organization_id = r_base_trx.organization_id;
SELECT unit_meas_lookup_code, unit_price
FROM po_lines_all
WHERE po_line_id = r_base_trx.po_line_id;
SELECT po_distribution_id, wip_entity_id, wip_line_id,
wip_repetitive_schedule_id, wip_operation_seq_num
FROM po_distributions_all
WHERE line_location_id = r_base_trx.po_line_location_id
AND ROWNUM = 1;
SELECT primary_item_id
FROM wip_entities
WHERE wip_entity_id = cp_wip_entity;
SELECT osp_return_days, osp_excise_percent
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = r_base_trx.organization_id;
SELECT pll.list_price
FROM so_price_list_lines pll,
JAI_CMN_VENDOR_SITES jvs
WHERE pll.price_list_id = jvs.price_list_id
AND pll.inventory_item_id = cp_item_id
AND jvs.vendor_id = r_base_trx.vendor_id
AND jvs.vendor_site_id = r_base_trx.vendor_site_id
AND Unit_Code = cp_uom_code
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT pll.list_price
FROM so_price_list_lines pll,
JAI_CMN_VENDOR_SITES jvs
WHERE pll.price_list_id = jvs.price_list_id
AND pll.inventory_item_id = cp_item_id
AND jvs.vendor_id = r_base_trx.vendor_id
AND jvs.vendor_site_id = 0
AND Unit_Code = cp_uom_code
AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
SELECT UOM_CODE
FROM mtl_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = cp_measure_code;
SELECT vendor_site_id
FROM po_headers_all
WHERE po_header_id = cp_header_id;
SELECT form_id
FROM JAI_PO_OSP_HDRS
WHERE po_header_id = p_po_header_id
AND primary_flag = 'Y'
AND issue_approved = 'Y'
-- AND receipt_approved = 'N' Sriram - bug# 3303027
AND (oth_doc_id = p_oth_doc_id OR oth_doc_id is NULL);
INSERT INTO JAI_PO_OSP_HDRS (
FORM_ID,
OTH_DOC_ID,
PO_HEADER_ID,
VENDOR_ID,
VENDOR_SITE_ID,
PROCESS_TIME,
ORGANIZATION_ID,
LOCATION_ID,
SOURCE,
ISSUE_APPROVED,
RECEIPT_APPROVED,
CANCEL_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PRIMARY_FORM_ID -- cbabu for Bug# 2746952
) VALUES (
JAI_PO_OSP_HDRS_S.nextval,
v_po_release_id,
v_po_header,
v_vendor,
v_vendor_site,
v_process_time,
v_org_id,
v_loc_id,
lv_source, --'RETURN TO VENDOR', /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
'N',
'N',
'N',
v_last_upd_dt,
v_last_upd_by,
v_creation_dt,
v_created_by,
v_last_upd_lgin,
v_primary_form_id -- cbabu for Bug# 2746952
) RETURNING form_id into v_form_id;
INSERT INTO JAI_PO_OSP_LINES (
FORM_ID,
LINE_ID,
PO_LINE_ID,
PO_DISTRIBUTION_ID,
ITEM_ID,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQUENCE_NUM,
ASSEMBLY_ID,
DESPATCH_QTY,
ITEM_UOM,
ITEM_VALUE,
TARIFF_CODE,
EXCISE_RATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PARENT_ITEM_ID,
COMP_QTY_PA,
BAL_PARENT_ITEM_QTY,
SOURCE_CODE
) VALUES(
v_form_id,
JAI_PO_OSP_LINES_S.nextval,
v_po_line,
v_po_dist,
v_item_id,
v_wip_entity,
v_wip_line,
v_wip_sch,
v_wip_oprn,
v_assembly,
v_issue_qty,
v_uom,
v_unit_price ,
v_item_tariff ,
NVL(v_excise_rate,0),
v_last_upd_dt,
v_last_upd_by,
v_created_by,
v_creation_dt,
v_last_upd_lgin,
v_item_id,
1,
v_issue_qty,
v_source_code
) RETURNING line_id into v_line_id;
PROCEDURE update_57f4_on_receiving
(
p_shipment_header_id NUMBER,
p_shipment_line_id NUMBER,
p_to_organization_id NUMBER,
p_ship_to_location_id NUMBER,
p_item_id NUMBER,
p_tran_type RCV_TRANSACTIONS.transaction_type%TYPE,
p_rcv_tran_qty RCV_TRANSACTIONS.quantity%TYPE,
p_new_primary_unit_of_measure RCV_SHIPMENT_LINES.primary_unit_of_measure%TYPE,
p_old_primary_unit_of_measure RCV_SHIPMENT_LINES.primary_unit_of_measure%TYPE,
p_unit_of_measure RCV_SHIPMENT_LINES.unit_of_measure%TYPE,
p_po_header_id NUMBER,
p_po_release_id NUMBER,
p_po_line_id NUMBER,
p_po_line_location_id NUMBER,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_creation_date DATE
)
IS
v_debug BOOLEAN; --File.Sql.35 Cbabu := false;
v_utl_file_name VARCHAR2(100); --File.Sql.35 Cbabu := 'update_57f4_on_receiving.log';
select unit_meas_lookup_code
from po_line_locations_all
where line_location_id = p_po_line_location_id;
select unit_meas_lookup_code
from po_lines_all
where po_line_id = p_po_line_id;
SELECT count(1)
FROM JAI_PO_OSP_HDRS
WHERE
-- nvl(receipt_approved,'N') != 'Y' AND - sriram - bug# 3303027
cancel_flag = 'N'
AND trunc(issue_date) + NVL(process_time,0) >= trunc(sysdate) -- NVL Condition added by sriram - bug# 3021456
AND po_header_id = p_po_header_id
AND (oth_doc_id IS NULL OR oth_doc_id = p_po_release_id)
AND organization_id = p_to_organization_id
AND location_id = p_ship_to_location_id
-- AND (source = 'PURCHASE ORDER' OR source = 'PO RELEASE' OR source = 'RETURN TO VENDOR')
AND NVL(primary_flag,'N') = 'Y'; --added on 06-jan-2000 gaurav.
SELECT form_id
FROM JAI_PO_OSP_HDRS
WHERE
--nvl(receipt_approved,'N') != 'Y' AND - sriram - bug# 3303027
cancel_flag = 'N'
AND trunc(issue_date) + NVL(process_time,0) >= trunc(sysdate)
AND po_header_id = p_po_header_id
AND (oth_doc_id IS NULL OR oth_doc_id = p_po_release_id)
AND organization_id = p_to_organization_id
AND location_id = p_ship_to_location_id
-- AND (source = 'PURCHASE ORDER' OR source = 'PO RELEASE' OR source = 'RETURN TO VENDOR')
AND NVL(primary_flag,'N') = 'Y'; --added on 06-jan-2000 gaurav.
SELECT wip_entity_id
FROM po_distributions_all
WHERE line_location_id = p_po_line_location_id
AND rownum = 1;
SELECT form_id, line_id, parent_item_id, item_id, bal_parent_item_qty,
comp_qty_pa, despatch_qty, return_qty, item_uom, po_distribution_id
-- 2746952
-- , ( despatch_qty - NVL(return_qty,0)) / comp_qty_pa balance_qty
, decode( v_match_type, 1, despatch_qty - NVL(return_qty,0), return_qty ) / comp_qty_pa balance_qty
, despatch_qty / comp_qty_pa despatch_parent_item_qty
-- , po_line_id
FROM JAI_PO_OSP_LINES
WHERE form_id = p_form_id
-- AND bal_parent_item_qty > 0
-- 2746952
AND ( ( v_match_type = 1 AND ( despatch_qty - NVL(return_qty,0) ) > 0 )
OR
( v_match_type = -1 AND return_qty > 0 )
)
AND po_line_Id = p_po_line_id
AND nvl(comp_qty_pa,0) <> 0 --vkaranam for bug#4607506
-- AND wip_entity_id = v_wip_entity_id
-- ORDER BY form_id, po_line_id;
Select unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = um;
select quantity_ordered
from po_distributions_all
where po_header_id = p_po_hdr_id
and po_line_id = p_po_line_id;
SELECT primary_uom_code, primary_unit_of_measure
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inv_item_id;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.update_57f4_on_receiving';
SELECT *
FROM mtl_system_items
WHERE organization_id = cp_org_id
AND inventory_item_id = cp_item_id;
* selected all the values
*/
(SELECT *
FROM JAI_PO_OSP_LINES WHERE form_id = pItemId AND po_line_id = p_po_line_id) LOOP
/* Bug 7028169. Added by Lakshmi Gopalsami
* Added logic for initializing the pl/sql table for
* resource unit type
* We need to proportionate the receipt quantity
* with that of PO qty and 57F4 despatch qty
*/
IF r_get_po_item_details.outside_operation_uom_type ='RESOURCE'
AND ii.source_code ='W' THEN
vProQty := (ii.original_qty/ pPoQty) * pQtyToApply;
CHANGE HISTORY: FILENAME: ja_in_update_57F4.sql
S.No Date Author and Details
------------------------------------------------------------------------------------------
1 01-JUN-2001 Satya Added DUAL UOM functionality
2 29-OCT-2002 Nagaraj.s for Bug2643016
As Functionally required, an Update statement is written to update the CR_REG_ENTRY_DATE of
the ja_in_57f4_table. This will definitely have implications on Approve 57f4 receipt screen on
Modvat claim but since no Modvat claim is available for 57f4 register, this has been approved
functionally.
3 22-JAN-2003 cbabu for Bug#2746952, FileVersion# 615.2
During the RETURN TO VENDOR transaction for the shipment line, the code is getting executed and
return quantity is getting updated. This is happening when a partial receipt is made and then RTV
is made for the same
4. 08-JAN-2004 ssumaith - bug# 3303027 File Version # 618.1
When the primary form is receipt_approved = 'Y' , when a receipt is made , return_quantity
field does not get updated for the RTV OSP form . There is a check in this procedure which
is preventing the entry of control into the code paths which update the RTV form.
5. 01-mar-2004 ssumaith - bug# 3446045 file version 618.2.
unit_meas_lookup_code column in the po_line_locations_all table is null in the clients
instance . The value in this field is being used as a basis of uom comparison. This
is causing wrong uom conversion and return quantuty is not getting updated correctly
when uom is changed.
This has been corrected by using the unit_meas_lookup_code of the po_lines_all table
in case the value retreived from the po_line_locations_all table is null.
6 03-JUN-2004 Vijay Shankar for Bug# 3644845, Version:115.1
return quantity is not getting updated when one of the OSP component is sent through Secondary form because the
code assumes that the components related to n OSP items will be sent in one form. Now the code is modified by
adding an internal function processCompBalances and calling it from code during following events
1) before main processing loop for initialization of comp balances to be updated on 57f4 lines
2) after every form, whether any balances for components are left
3) start of each line being processed, for remaining quantity to be applied on 57f4 line
4) end of lines loop to update plsql table with quantity applied onto the line
New function is written as a central code which manages the component quantities remaining to be applied onto 57f4 lines
--------------------------------------------------------------------------------------------*/
/*
--File.Sql.35 Cbabu
v_utl_file_name := 'update_57f4_on_receiving.log';
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL, Value,SUBSTR (value,1,INSTR(value,',') -1))
INTO v_utl_location
FROM v$parameter WHERE name = 'utl_file_dir';
SELECT '1' seq, form_id, source, form_id*v_match_type order_by
FROM JAI_PO_OSP_HDRS
WHERE form_id = v_primary_form_id
UNION
SELECT '2' seq, form_id, source, form_id*v_match_type order_by
FROM JAI_PO_OSP_HDRS
WHERE primary_form_id = v_primary_form_id
AND NVL(receipt_approved,'N') <> 'Y'
AND issue_approved = 'Y'
ORDER BY order_by
)
LOOP
vFormId_ToBeUpdWithRegDate := null;
v_quantity_applied := v_bal_qty; -- Vijay Shankar for Bug# 3644845, quantity on 57F4 line is Complete. Should update next 57F4 line with remaining qty
v_quantity_applied := -v_bal_qty; -- Vijay Shankar for Bug# 3644845, quantity on 57F4 line is Complete. Should update next 57F4 line with remaining qty
UPDATE JAI_PO_OSP_LINES
SET return_qty = round((nvl(return_qty,0) + nvl(v_ret_fact,1) * v_return_qty), 5),
bal_parent_item_qty = v_bal_qty,
last_update_date = sysdate,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
where line_id = each_line.line_id;
jai_cmn_utils_pkg.print_log(v_utl_file_name,'5.3 updatedCount -> '|| SQL%ROWCOUNT
||', v_left_received_qty -> '|| v_left_received_qty
);
UPDATE JAI_PO_OSP_HDRS
SET CR_REG_ENTRY_DATE = v_creation_date
WHERE form_id = vFormId_ToBeUpdWithRegDate;
END update_57f4_on_receiving;