[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Included the issue_date column in the insert statement
insert into JAI_PO_OSP_HDRS, with value as sysdate.
23-SEP-2011 12977826 by anupgupt
description: APPROVE 57F(4) DISPATCH FOPRM NOT ALLOWING
TO CREATE 57F(4) CHALLAN IF 1ST CHALL
fix: corrected cursor query c_header_exists where
conditions grouping
06-DEC-2011 13541366 Description :ISSUE IN UPDATION OF RETURN QTY AND CANCELLATION
OF PRIMARY CHALLAN IN 57F4 FORM
fix: corrected cursor query c_header_id where
conditions grouping and added cancel_flag condition.
Reverted back the fix done for 9626826
In cance_osp procedure, in the IF concition added
the condition for form number also, if for a challan
challan number is generated then only the dispatched qty
is updated in the primary challan when the secondary
challan is cancelled.
19-APR-2012 13963867 Description - ORIGINAL QTY AND DISPATCH QTY FIELD ARE NOT DEFAULTED
CORRECTLY IN 57F4 FORM for operation type RESOURCE
Fix - While updating the quantity for type RESOURCE, the original
quantity and dispatch qty are calculated based on the required
quantity of the job multiplied by the qty per assembly factor.
But the required qty of the job itself is derived based on the
total po quantity multiplied by the qty per assembly. In the code
we are again multiplying the required qty with the assembly rate
which is causing the issue. Fixed the same and now the quantities
are fetched based on required quantity for RESOURCE type operations
----------------------------------------------------------------------------------------------*/
PROCEDURE ja_in_57F4_process_header
(p_po_header_id po_headers_all.po_header_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
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.ja_in_57F4_process_header';
"Cannot insert null into ..." error.
Fix : Added an ELSE part to define those variables for cases which were
not handled.
8. 16-Jul-2009 Bug 8602495 File version 120.1.12000000.8 / 120.4.12010000.6 / 120.11
Issue : The 57F4 form shows the PO item (defined as OSP item), even if
it is the first item in routing sequence.
Fix : Forward ported changes done for 11i bugs 4680221 and 5017903
(corresponding R12 FP bugs - 4940629 and 5072683).
Following changes are done:
1)Cursor c_get_rout_status will get the data from wip_operations
instead of bom_operation_sequences.
2)Cursor c_discrete_bill_seq_id (and its use) is removed. Data
from the po_dist record will be used instead.
3)Modified the filter condition in the cursor of c_component_rec.
9. 23-Jul-2009 Bug 8678948 AFTER RTV PRIMARY FORM IN APPROVE 57F4 CHALLAN NOT ABLE TO APPROVE THE DISPATCH.
issue : The original_qty column is not populated in the table JAI_PO_OSP_LINES in the procedure
create_rcv_57f4
Fix: populated rtv_qty in the column original_qty
----------------------------------------------------------------------------------------*/
/* Added by Ramananda for removal of SQL LITERALs */
lv_called_release := 'RELEASE';
(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
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','2 Inside for loop po lineid '||c_Line_rec.po_line_id||' called from '||p_called_from||' ');
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')) -- for bug#12977826 by anupgupt
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')) -- added for bug #13541366
AND organization_id = cp_org_id
AND location_id = cp_loc_id
AND nvl(cancel_flag,'N') = 'N'; -- added for bug #13541366
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 wip_operations
WHERE wip_entity_id = cp_wip_entity_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;
select usage_rate_or_amount
from wip_operation_resources
where wip_entity_id = cp_wip_entity_id
and operation_seq_num = cp_op_seq_num
and resource_id in (select resource_id
from bom_resources
where purchase_item_id = cp_item_id);
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.ja_in_57f4_lines_insert';
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','3 Inside insert lines procedure '||' ');
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','4 Inside insert lines ln_check_57f4_exists value '||ln_check_57f4_exists||' ');
(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
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','5 Inside for loop line id'||rec_po_line_cur.po_line_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,
ISSUE_DATE -- added for bug 8303018
)
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',
sysdate -- added for bug 8303018
);
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','9 after insert into hdrs ');
( 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_reqmt_op_ctr + ln_ja_ctr = 0) /*Bug 4609260*/
/*above condition changed for bug 8602495*/
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
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','10 inside lines cursor ');
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, dtl.form_id, hdr.form_number -- added from number for 13541366
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
);
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1.2 Before Update ');
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;
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1.3 After Update '||SQL%ROWCOUNT);
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,
ORIGINAL_QTY, /* added for bug 8678948 by vumaasha */
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,/* added for bug 8678948 vumaasha */
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 uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = um;
Select item_uom, source_code
from jai_po_osp_lines
where form_id = v_primary_form_id
and po_line_id = p_po_line_id;
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';
jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1 update receiving quantity ');
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 * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- 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 * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- 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;