[Home] [Help]
167:
168: cursor l_LineVsLotQty_csr
169: is
170: select plnt.line_number
171: from pos_exasn_lines plnt, pos_exasn_lots plot
172: where plnt.line_id = plot.line_id
173: group by plnt.line_number, plnt.quantity
174: having plnt.quantity <> sum(plot.quantity)
175: and sum(plot.quantity) > 0;
176:
177: cursor l_LineVsSerialQty_csr
178: is
179: select plnt.line_number
180: from pos_exasn_lines plnt, pos_exasn_serials pst
181: where plnt.line_id = pst.line_id
182: group by plnt.line_number, plnt.quantity
183: having plnt.quantity <> sum(pst.quantity)
184: and sum(pst.quantity) > 0;
185:
186: cursor l_lineVsLpnQty_csr
187: is
188: select plnt.line_number
189: from pos_exasn_lines plnt, pos_exasn_lpns plpn
190: where plnt.line_id = plpn.line_id
191: group by plnt.line_number, plnt.quantity
192: having plnt.quantity <> sum(plpn.quantity)
193: and sum(plpn.quantity) > 0;
403: and plot.lot_id = pst.lot_id);
404:
405: update pos_exasn_lpns plpn
406: set (plpn.po_line_loc_id) = (select plnt.po_line_location_id
407: from pos_exasn_lines plnt
408: where plnt.header_id = plpn.header_id
409: and plnt.line_id = plpn.line_id);
410:
411: update pos_exasn_lots plot
409: and plnt.line_id = plpn.line_id);
410:
411: update pos_exasn_lots plot
412: set (plot.po_line_loc_id) = (select plnt.po_line_location_id
413: from pos_exasn_lines plnt
414: where plnt.header_id = plot.header_id
415: and plnt.line_id = plot.line_id);
416: update pos_exasn_serials pst
417: set (pst.po_line_loc_id) = (select plnt.po_line_location_id
414: where plnt.header_id = plot.header_id
415: and plnt.line_id = plot.line_id);
416: update pos_exasn_serials pst
417: set (pst.po_line_loc_id) = (select plnt.po_line_location_id
418: from pos_exasn_lines plnt
419: where plnt.header_id = pst.header_id
420: and plnt.line_id = pst.line_id);
421:
422: open l_checkLot_csr;
803: --L1: UOM
804: cursor l_checkUOM_csr
805: is
806: select plnt.line_number, plnt.uom
807: from pos_exasn_lines plnt
808: where not exists
809: (select 1 from por_unit_of_measure_lov_v puomv
810: where puomv.unit_of_measure = plnt.uom);
811:
812: --L2: Country of Origin
813: cursor l_checkCountryOO_csr
814: is
815: select plnt.line_number, plnt.country_of_origin
816: from pos_exasn_lines plnt
817: where plnt.country_of_origin is not null
818: and not exists
819: (select 1 from fnd_territories_vl ftv
820: where ftv.territory_code = plnt.country_of_origin);
823: --L3/L4: Check for valid Organization Name and PO/Line/Shipment Number
824: cursor l_checkOrgName_csr
825: is
826: select line_number, operating_unit
827: from pos_exasn_lines
828: where org_id is null;
829:
830: cursor l_checkPO_csr
831: is
835: plnt.po_revision,
836: plnt.po_line,
837: plnt.po_shipment,
838: plnt.operating_unit
839: from pos_exasn_lines plnt
840: where plnt.po_header_id is null
841: and plnt.po_release_num is null;
842:
843: cursor l_checkREL_csr
848: plnt.po_release_num,
849: plnt.po_revision,
850: plnt.po_shipment,
851: plnt.operating_unit
852: from pos_exasn_lines plnt
853: where plnt.po_header_id is null
854: and plnt.po_release_num is not null;
855:
856: --L5: Check for Securing Attributes
856: --L5: Check for Securing Attributes
857: cursor l_vendors_csr
858: is
859: select line_number,vendor_id
860: from pos_exasn_lines;
861:
862: cursor l_checkVendorSites_csr(x_user_id number)
863: is
864: select line_number
861:
862: cursor l_checkVendorSites_csr(x_user_id number)
863: is
864: select line_number
865: from pos_exasn_lines plnt
866: where not exists(
867: select 1
868: from ak_web_user_sec_attr_values
869: WHERE web_user_id = x_user_id
873:
874: cursor l_checkVendorContacts_csr(x_user_id number)
875: is
876: select line_number
877: from pos_exasn_lines plnt
878: where not exists(
879: select 1
880: from ak_web_user_sec_attr_values
881: WHERE web_user_id = x_user_id
888: l_convQty number;
889:
890: l_primary_qty number;
891: l_line_number number;
892: l_uom pos_exasn_lines.uom%type;
893: l_po_line_location_id number;
894: l_tolerableShipQty number;
895: l_item_id number;
896: cursor l_allLines_csr
895: l_item_id number;
896: cursor l_allLines_csr
897: is
898: select line_number, quantity, uom, po_line_location_id, item_id, header_id
899: from pos_exasn_lines;
900:
901:
902:
903: --L7: Check for existing ASN with same Shipment Number
908: select line_number, shipment_number
909: from
910: (
911: select pht.line_number, pht.shipment_number
912: from pos_exasn_lines plnt,
913: rcv_headers_interface rhi,
914: pos_exasn_headers pht
915: where pht.header_id = plnt.header_id
916: and pht.shipment_number = rhi.shipment_num
917: and plnt.vendor_id = rhi.vendor_id
918: and nvl(plnt.vendor_site_id, -9999) = nvl(rhi.vendor_site_id, -9999)
919: union
920: select plnt.line_number, pht.shipment_number
921: from pos_exasn_lines plnt,
922: rcv_shipment_headers rsh,
923: pos_exasn_headers pht
924: where pht.header_id = plnt.header_id
925: and pht.shipment_number = rsh.shipment_num
974: close l_checkCountryOO_csr;
975:
976:
977: --L3: Organization Name
978: update pos_exasn_lines plnt
979: set plnt.org_id = (
980: select hou.organization_id
981: from hr_operating_units hou
982: where hou.name = plnt.operating_unit);
996: end loop;
997: close l_checkOrgName_csr;
998:
999: --L4: Valid Org/PO/Line/Shipment Number
1000: update pos_exasn_lines plnt
1001: set (
1002: po_header_id,
1003: po_line_id,
1004: po_line_location_id,
1076: )
1077: where org_id is not null
1078: and po_release_num is null;
1079:
1080: select count(1) into l_error_ln from pos_exasn_lines where po_line_location_id is null;
1081:
1082:
1083: update pos_exasn_lines plnt
1084: set (
1079:
1080: select count(1) into l_error_ln from pos_exasn_lines where po_line_location_id is null;
1081:
1082:
1083: update pos_exasn_lines plnt
1084: set (
1085: po_header_id,
1086: po_release_id,
1087: po_line_id,
1637:
1638: procedure UpdateLinesAndLls(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
1639: l_error_pointer in out NOCOPY number)
1640: IS
1641: l_uom pos_exasn_lines.uom%type;
1642: l_po_line_location_id number;
1643: l_tolerableShipQty number;
1644: l_item_id number;
1645: l_quantity number;
1650:
1651: cursor l_allLines_csr
1652: is
1653: select line_number, quantity, uom, po_line_location_id, item_id, line_id
1654: from pos_exasn_lines;
1655:
1656: BEGIN
1657: -- Update All lines with Primary Quantity and Invoiced Quantity
1658: open l_allLines_csr;
1672: null;
1673: end if;
1674: else
1675:
1676: update pos_exasn_lines
1677: set primary_quantity = l_primary_qty
1678: where line_id = l_line_id;
1679:
1680: update pos_exasn_lines
1676: update pos_exasn_lines
1677: set primary_quantity = l_primary_qty
1678: where line_id = l_line_id;
1679:
1680: update pos_exasn_lines
1681: set invoiced_quantity = POS_QUANTITIES_S.get_invoice_qty
1682: (l_po_line_location_id,
1683: l_uom,
1684: l_item_id,
1689: close l_allLines_csr;
1690:
1691:
1692:
1693: update pos_exasn_lines plnt
1694: set lpn_group_id = (select pht.lpn_group_id
1695: from pos_exasn_headers pht
1696: where pht.header_id = plnt.header_id)
1697: where
1720:
1721:
1722: update pos_exasn_lots plot
1723: set (plot.product_transaction_id, plot.uom) =(select plnt.interface_transaction_id, plnt.uom
1724: from pos_exasn_lines plnt
1725: where plnt.header_id = plot.header_id
1726: and plnt.line_id = plot.line_id);
1727:
1728:
1727:
1728:
1729: update pos_exasn_serials pst
1730: set (pst.product_transaction_id) = (select plnt.interface_transaction_id
1731: from pos_exasn_lines plnt
1732: where plnt.header_id = pst.header_id
1733: and plnt.line_id = pst.line_id);
1734:
1735: END UpdateLinesAndLls;
1753: select
1754: plnt.header_id,
1755: plnt.line_id,
1756: plnt.quantity
1757: from pos_exasn_lines plnt
1758: where lls_code = 'SER';
1759: /*
1760: and (select count(distinct nvl(license_plate_number,'null'))
1761: from pos_exasn_serials pst
1774:
1775: l_line_id number;
1776:
1777: begin
1778: select max(line_id)+1 into l_new_line from pos_exasn_lines;
1779:
1780: open l_SerLines_csr;
1781: loop
1782: fetch l_SerLines_csr into
1791: l_ser_lpn;
1792: exit when l_distinctLpn_ser_csr%NOTFOUND;
1793: if(l_first_ser = 'T') then
1794: l_first_ser := 'F';
1795: update pos_exasn_lines
1796: set license_plate_number = l_ser_lpn
1797: where line_id = l_line_id;
1798:
1799: if(l_ser_lpn is null) then
1796: set license_plate_number = l_ser_lpn
1797: where line_id = l_line_id;
1798:
1799: if(l_ser_lpn is null) then
1800: update pos_exasn_lines plnt
1801: set plnt.quantity =
1802: (select sum(quantity)
1803: from pos_exasn_serials pst
1804: where pst.header_id = plnt.header_id
1805: and pst.line_id = plnt.line_id
1806: and pst.license_plate_number is null)
1807: where plnt.line_id = l_line_id;
1808: else
1809: update pos_exasn_lines plnt
1810: set plnt.quantity =
1811: (select sum(quantity)
1812: from pos_exasn_serials pst
1813: where pst.header_id = plnt.header_id
1866: end if;
1867: end loop;
1868: close l_SerLines_csr;
1869: /*
1870: update pos_exasn_lines plnt
1871: set license_plate_number = (select distinct pst2.license_plate_number
1872: from pos_exasn_serials pst2
1873: where pst2.line_id = plnt.line_id
1874: and pst2.license_plate_number is not null)
1898: select
1899: plnt.header_id,
1900: plnt.line_id,
1901: plnt.quantity
1902: from pos_exasn_lines plnt
1903: where lls_code = 'LPN';
1904: /*
1905: and (select count(distinct nvl(license_plate_number,'null'))
1906: from pos_exasn_lpns plpn
1920:
1921: l_line_id number;
1922:
1923: begin
1924: select max(line_id)+1 into l_new_line from pos_exasn_lines;
1925:
1926: open l_LpnLines_ML_csr;
1927: loop
1928: fetch l_LpnLines_ML_csr into
1937: l_lpn;
1938: exit when l_distinctLpn_csr%NOTFOUND;
1939: if(l_first_lpn = 'T') then
1940: l_first_lpn := 'F';
1941: update pos_exasn_lines
1942: set license_plate_number = l_lpn
1943: where line_id = l_line_id;
1944:
1945: update pos_exasn_lines plnt
1941: update pos_exasn_lines
1942: set license_plate_number = l_lpn
1943: where line_id = l_line_id;
1944:
1945: update pos_exasn_lines plnt
1946: set plnt.quantity = (select plpn.quantity
1947: from pos_exasn_lpns plpn
1948: where plpn.header_id = plnt.header_id
1949: and plpn.line_id = plnt.line_id
1988: end if;
1989: end loop;
1990: close l_LpnLines_ML_csr;
1991: /*
1992: update pos_exasn_lines plnt
1993: set license_plate_number = (select distinct plpn2.license_plate_number
1994: from pos_exasn_lpns plpn2
1995: where plpn2.line_id = plnt.line_id
1996: and plpn2.license_plate_number is not null)
2019: select
2020: plnt.header_id,
2021: plnt.line_id,
2022: plnt.quantity
2023: from pos_exasn_lines plnt
2024: where lls_code in ('LOT','LAS');
2025: /*
2026: and (select count(distinct nvl(license_plate_number,'null'))
2027: from pos_exasn_lots plot
2040:
2041: l_line_id number;
2042:
2043: begin
2044: select max(line_id)+1 into l_new_line from pos_exasn_lines;
2045:
2046: open l_LotLines_ML_csr;
2047: loop
2048: fetch l_LotLines_ML_csr into
2060:
2061: if(l_first_lot = 'T') then
2062: l_first_lot := 'F';
2063:
2064: update pos_exasn_lines
2065: set license_plate_number = l_lot_lpn
2066: where line_id = l_line_id;
2067:
2068: if(l_lot_lpn is null) then
2065: set license_plate_number = l_lot_lpn
2066: where line_id = l_line_id;
2067:
2068: if(l_lot_lpn is null) then
2069: update pos_exasn_lines plnt
2070: set plnt.quantity =
2071: (select sum(quantity)
2072: from pos_exasn_lots plot
2073: where plot.header_id = plnt.header_id
2074: and plot.line_id = plnt.line_id
2075: and plot.license_plate_number is null)
2076: where plnt.line_id = l_line_id;
2077: else
2078: update pos_exasn_lines plnt
2079: set plnt.quantity =
2080: (select sum(quantity)
2081: from pos_exasn_lots plot
2082: where plot.header_id = plnt.header_id
2152: end if;
2153: end loop;
2154: close l_LotLines_ML_csr;
2155: /*
2156: update pos_exasn_lines plnt
2157: set license_plate_number = (select distinct plot2.license_plate_number
2158: from pos_exasn_lots plot2
2159: where plot2.line_id = plnt.line_id
2160: and plot2.license_plate_number is not null)
2250: plot.NATTRIBUTE8,
2251: plot.NATTRIBUTE9,
2252: plot.NATTRIBUTE10
2253: from pos_exasn_lots plot,
2254: pos_exasn_lines plnt
2255: where plot.line_id = plnt.line_id;
2256:
2257: cursor l_allSerials_csr
2258: is
2309: pst.NATTRIBUTE8,
2310: pst.NATTRIBUTE9,
2311: pst.NATTRIBUTE10
2312: from pos_exasn_serials pst,
2313: pos_exasn_lines plnt
2314: where pst.line_id = plnt.line_id;
2315:
2316:
2317: cursor l_allLpns_csr
2322: plpn.parent_lpn,
2323: plnt.lpn_group_id,
2324: plpn.line_number
2325: from pos_exasn_lpns plpn,
2326: pos_exasn_lines plnt
2327: where plnt.line_id = plpn.line_id;
2328: l_txn_intf_id number;
2329: l_ser_intf_id number;
2330: l_lot_number pos_exasn_lots.lot_number%type;
2853: (
2854: select
2855: count(1), pht.header_id HEADER_ID, pht.shipment_number SHIPMENT_NUMBER, plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code
2856: from pos_exasn_headers pht,
2857: pos_exasn_lines plnt
2858: where pht.header_id = plnt.header_id
2859: group by pht.header_id, pht.shipment_number, plnt.vendor_id, plnt.ship_to_org_id, nvl(plnt.vendor_site_id, -9999), plnt.currency_code
2860: )
2861: group by HEADER_ID
2862: having count(1) > 1;
2863:
2864: cursor l_createExtraRhi_csr(x_header_id number) is
2865: select distinct plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code CURRENCY_CODE
2866: from pos_exasn_lines plnt
2867: where header_id = x_header_Id;
2868:
2869: l_asn_asbn varchar2(10);
2870: l_early_exit exception;
2957:
2958: l_asn_header_id := l_asn_header_id + 1;
2959: CreateNewHeader(l_asn_header_id, l_ex_header_id, l_ex_vendor_id, l_ex_ship_to_org_id, l_ex_vendor_site_id );
2960:
2961: update pos_exasn_lines
2962: set header_id = l_asn_header_id
2963: where header_id = l_ex_header_id
2964: and vendor_id = l_ex_vendor_id
2965: and ship_to_org_id = l_ex_ship_to_org_id
2992: plnt.currency_code,
2993: plnt.rate,
2994: plnt.rate_type,
2995: plnt.rate_date
2996: from pos_exasn_lines plnt
2997: where plnt.header_id = pht.header_id
2998: and plnt.line_id =
2999: (select min(plnt2.line_id)
3000: from pos_exasn_lines plnt2
2996: from pos_exasn_lines plnt
2997: where plnt.header_id = pht.header_id
2998: and plnt.line_id =
2999: (select min(plnt2.line_id)
3000: from pos_exasn_lines plnt2
3001: where plnt2.header_id = pht.header_id)
3002: );
3003:
3004: --H1 (Has to be done after PHT.SHIP_TO_ORG_ID is populated)
3061:
3062:
3063:
3064:
3065: update pos_exasn_lines plnt
3066: set (header_interface_id, group_id, expected_receipt_date) = (select pht.header_interface_id, pht.group_id, pht.expected_receipt_date
3067: from pos_exasn_headers pht
3068: where pht.header_id = plnt.header_id);
3069: exception when l_early_exit then
3073: procedure CreateNewLine(p_qty in number, p_lpn in varchar2, p_line_id in number, p_old_ln in number)
3074: is
3075: l_intf_txn_id number;
3076: begin
3077: insert into pos_exasn_lines(line_id, quantity, license_plate_number) values(p_line_id, p_qty, p_lpn );
3078: select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_intf_txn_id from dual;
3079: update pos_exasn_lines
3080: set (
3081: PRIMARY_UOM,
3075: l_intf_txn_id number;
3076: begin
3077: insert into pos_exasn_lines(line_id, quantity, license_plate_number) values(p_line_id, p_qty, p_lpn );
3078: select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_intf_txn_id from dual;
3079: update pos_exasn_lines
3080: set (
3081: PRIMARY_UOM,
3082: LPN_GROUP_ID,
3083: EXPECTED_RECEIPT_DATE,
3272: SHIP_TO_LOCATION_CODE,
3273: SHIP_TO_LOCATION_ID,
3274: LLS_CODE,
3275: ITEM_DESCRIPTION
3276: from pos_exasn_lines
3277: where line_id = p_old_ln)
3278: where line_id = p_line_id;
3279: end CreateNewLine;
3280:
3426: l_25errors exception;
3427: l_error_ln number;
3428: cursor l_checkLotControl_csr is
3429: select distinct plnt.line_number
3430: from pos_exasn_lines plnt,
3431: pos_exasn_lots plot
3432: where plnt.lls_code not in ('LOT','LAS')
3433: and plnt.line_id = plot.line_id;
3434:
3433: and plnt.line_id = plot.line_id;
3434:
3435: cursor l_checkSerialControl_csr is
3436: select distinct plnt.line_number
3437: from pos_exasn_lines plnt,
3438: pos_exasn_serials pst
3439: where plnt.lls_code not in ('SER','LAS')
3440: and plnt.line_id = pst.line_id;
3441:
3451: a1.license_plate_number LPN,
3452: a1.line_number LINE_NUM
3453: from
3454: pos_exasn_lpns a1,
3455: pos_exasn_lines ln
3456: where a1.line_id = ln.line_id
3457: and ln.lls_code in ('LOT','LAS')
3458: and not exists
3459: ( select 1
3472: a1.license_plate_number LPN,
3473: a1.line_number LINE_NUM
3474: from
3475: pos_exasn_lpns a1,
3476: pos_exasn_lines ln
3477: where a1.line_id = ln.line_id
3478: and ln.lls_code = 'SER'
3479: and not exists
3480: ( select 1
3495: a1.line_number LINE_NUM
3496:
3497: from
3498: pos_exasn_lpns a1,
3499: pos_exasn_lines ln
3500: where a1.line_id = ln.line_id
3501: and ln.lls_code = 'LPN'
3502: and a1.quantity is not null
3503: and exists ( select 1
3513: a1.license_plate_number LPN,
3514: a1.line_number LINE_NUM
3515: from
3516: pos_exasn_lpns a1,
3517: pos_exasn_lines ln
3518: where a1.line_id = ln.line_id
3519: and ln.lls_code = 'LPN'
3520: and a1.quantity is null
3521: and not exists ( select 1
3527:
3528: );
3529:
3530: BEGIN
3531: update pos_exasn_lines plnt
3532: set plnt.lls_Code = 'LAS'
3533: where exists(
3534: select /*+ INDEX (msi, mtl_system_items_b_u1) */
3535: 1 from mtl_system_items msi
3537: and msi.organization_id = plnt.org_id
3538: and msi.lot_control_code = 2
3539: and msi.serial_number_control_code in (2,5));
3540:
3541: update pos_exasn_lines plnt
3542: set plnt.lls_Code = 'LOT'
3543: where plnt.lls_code is null
3544: and exists(
3545: select /*+ INDEX (msi, mtl_system_items_b_u1) */
3548: and msi.organization_id = plnt.org_id
3549: and msi.lot_control_code = 2
3550: and msi.serial_number_control_code not in (2,5));
3551:
3552: update pos_exasn_lines plnt
3553: set plnt.lls_Code = 'SER'
3554: where plnt.lls_code is null
3555: and exists(
3556: select /*+ INDEX (msi, mtl_system_items_b_u1) */
3559: and msi.organization_id = plnt.org_id
3560: and msi.lot_control_code = 1
3561: and msi.serial_number_control_code in (2,5));
3562:
3563: update pos_exasn_lines plnt
3564: set plnt.lls_Code = 'LPN'
3565: where plnt.lls_code is null;
3566:
3567:
3725: license_plate_number,
3726: lpn_group_id,
3727: po_number,
3728: item_description
3729: from pos_exasn_lines;
3730:
3731: end InsertIntoRTI;
3732:
3733:
4100: cursor l_checkLpnContra_csr
4101: is
4102: select plpn1.line_number, plpn2.line_number
4103: from pos_exasn_lpns plpn1, pos_exasn_lpns plpn2,
4104: pos_exasn_lines plnt1, pos_exasn_lines plnt2
4105: where plpn1.license_plate_number = plpn2.license_plate_number
4106: and plpn1.parent_lpn <> plpn2.parent_lpn
4107: and plnt1.line_id = plpn1.line_id
4108: and plnt2.line_id = plpn2.line_id
4134: plt.po_line_location_id,
4135: pht.ship_from_location_code,
4136: ps.location_id as ship_from_location_id
4137: from pos_exasn_headers pht,
4138: pos_exasn_lines plt,
4139: hz_party_sites ps
4140: where pht.header_id = plt.header_id
4141: and pht.ship_from_location_code is not null
4142: and ps.party_site_number = pht.ship_from_location_code||'|'||pht.vendor_id
4148: l_return_status VARCHAR2(2000);
4149: l_prev_header_id pos_exasn_headers.header_id%type := -1;
4150: l_prev_ship_from VARCHAR2(30) := '';
4151: l_header_id pos_exasn_headers.header_id%type;
4152: l_line_id pos_exasn_lines.po_line_id%type;
4153: l_line_location_id pos_exasn_lines.po_line_location_id%type;
4154: l_ship_from_location_code pos_exasn_headers.ship_from_location_code%type;
4155: --l_ship_from_location_id hz_party_sites.location_id%type;
4156: l_ship_from_location_id number;
4149: l_prev_header_id pos_exasn_headers.header_id%type := -1;
4150: l_prev_ship_from VARCHAR2(30) := '';
4151: l_header_id pos_exasn_headers.header_id%type;
4152: l_line_id pos_exasn_lines.po_line_id%type;
4153: l_line_location_id pos_exasn_lines.po_line_location_id%type;
4154: l_ship_from_location_code pos_exasn_headers.ship_from_location_code%type;
4155: --l_ship_from_location_id hz_party_sites.location_id%type;
4156: l_ship_from_location_id number;
4157: l_err_tbl po_tbl_varchar2000 ;