[Home] [Help]
15: TYPE RCPT_LINES_Rec_Type IS RECORD
16: (
17: rma_number oe_order_headers_all.order_number%type,
18: rma_header_id oe_order_headers_all.header_id%type ,
19: line_id oe_order_lines_all.line_id%type ,
20: rma_line_number oe_order_lines_all.line_number%type ,
21: inventory_item_id oe_order_lines_all.inventory_item_id%type,
22: org_name hr_all_organization_units.name%type ,
23: organization_id rcv_transactions.organization_id%type,
16: (
17: rma_number oe_order_headers_all.order_number%type,
18: rma_header_id oe_order_headers_all.header_id%type ,
19: line_id oe_order_lines_all.line_id%type ,
20: rma_line_number oe_order_lines_all.line_number%type ,
21: inventory_item_id oe_order_lines_all.inventory_item_id%type,
22: org_name hr_all_organization_units.name%type ,
23: organization_id rcv_transactions.organization_id%type,
24: unit_of_measure rcv_transactions.unit_of_measure%type,
17: rma_number oe_order_headers_all.order_number%type,
18: rma_header_id oe_order_headers_all.header_id%type ,
19: line_id oe_order_lines_all.line_id%type ,
20: rma_line_number oe_order_lines_all.line_number%type ,
21: inventory_item_id oe_order_lines_all.inventory_item_id%type,
22: org_name hr_all_organization_units.name%type ,
23: organization_id rcv_transactions.organization_id%type,
24: unit_of_measure rcv_transactions.unit_of_measure%type,
25: received_quantity rcv_transactions.quantity%type ,
57: ro_rcvd_qty csd_repairs.quantity_rcvd%type,
58: inventory_item_id csd_repairs.inventory_item_id%type,
59: ro_uom csd_repairs.unit_of_measure%type,
60: requisition_number po_requisition_headers_all.segment1%type,
61: ordered_quantity oe_order_lines_all.ordered_quantity%type,
62: order_number oe_order_headers_all.order_number%type
63: );
64:
65: TYPE SHIP_LINES_Rec_Type IS RECORD
76: organization_id wsh_delivery_details.organization_id%type,
77: order_number wsh_delivery_details.source_header_number%type ,
78: sales_order_header wsh_delivery_details.source_header_id%type ,
79: locator_id wsh_delivery_details.locator_id%type,
80: order_line_number oe_order_lines_all.line_number%type ,
81: date_shipped oe_order_lines_all.actual_shipment_date%type ,
82: line_id oe_order_lines_all.line_id%type , --Bug#6779806
83: repair_number csd_repairs.repair_number%type,
84: repair_line_id csd_repairs.repair_line_id%type,
77: order_number wsh_delivery_details.source_header_number%type ,
78: sales_order_header wsh_delivery_details.source_header_id%type ,
79: locator_id wsh_delivery_details.locator_id%type,
80: order_line_number oe_order_lines_all.line_number%type ,
81: date_shipped oe_order_lines_all.actual_shipment_date%type ,
82: line_id oe_order_lines_all.line_id%type , --Bug#6779806
83: repair_number csd_repairs.repair_number%type,
84: repair_line_id csd_repairs.repair_line_id%type,
85: ro_uom csd_repairs.unit_of_measure%type ,
78: sales_order_header wsh_delivery_details.source_header_id%type ,
79: locator_id wsh_delivery_details.locator_id%type,
80: order_line_number oe_order_lines_all.line_number%type ,
81: date_shipped oe_order_lines_all.actual_shipment_date%type ,
82: line_id oe_order_lines_all.line_id%type , --Bug#6779806
83: repair_number csd_repairs.repair_number%type,
84: repair_line_id csd_repairs.repair_line_id%type,
85: ro_uom csd_repairs.unit_of_measure%type ,
86: ro_item_id csd_repairs.inventory_item_id%type ,
93: org_name hr_all_organization_units.name%type
94: );
95: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
96: (
97: header_id oe_order_lines_all.header_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
94: );
95: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
96: (
97: header_id oe_order_lines_all.header_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
95: TYPE IO_SHIP_LINES_Rec_Type IS RECORD
96: (
97: header_id oe_order_lines_all.header_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
96: (
97: header_id oe_order_lines_all.header_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
104: shipment_date oe_order_lines_all.actual_shipment_date%type ,
97: header_id oe_order_lines_all.header_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
104: shipment_date oe_order_lines_all.actual_shipment_date%type ,
105: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
98: line_id oe_order_lines_all.line_id%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
104: shipment_date oe_order_lines_all.actual_shipment_date%type ,
105: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
106: shipped_quantity wsh_delivery_details.shipped_quantity%type,
99: ordered_quantity oe_order_lines_all.ordered_quantity%type,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
104: shipment_date oe_order_lines_all.actual_shipment_date%type ,
105: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
106: shipped_quantity wsh_delivery_details.shipped_quantity%type,
107: del_line_serial_num wsh_serial_numbers.fm_serial_number%type ,
100: req_header_id oe_order_lines_all.source_document_id%type ,
101: req_line_id oe_order_lines_all.source_document_line_id%type ,
102: req_number oe_order_lines_all.orig_sys_document_ref%type ,
103: inventory_item_id oe_order_lines_all.inventory_item_id%type,
104: shipment_date oe_order_lines_all.actual_shipment_date%type ,
105: delivery_detail_id wsh_delivery_details.delivery_detail_id%type,
106: shipped_quantity wsh_delivery_details.shipped_quantity%type,
107: del_line_serial_num wsh_serial_numbers.fm_serial_number%type ,
108: lot_number wsh_delivery_details .lot_number%type,
2145: cs_estimate_details ced,
2146: csd_repairs cra,
2147: rcv_transactions rcvt,
2148: oe_order_headers_all oeh,
2149: oe_order_lines_all oel,
2150: hr_all_organization_units haou
2151: WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2152: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2153: AND ced.order_header_id is not null
2173: AND paramn1 = rcvt.transaction_id)
2174:
2175: AND ((ced.QUANTITY_REQUIRED < -1
2176: AND oel.line_id in ( Select line_id
2177: from oe_order_lines_all oel1
2178: start with oel1.line_id = ced.order_line_id
2179: connect by prior oel1.line_id = oel1.split_from_line_id
2180: and oel1.shipped_quantity is not null
2181: and oel1.header_id = oeh.header_id))
2222: cs_estimate_details ced,
2223: csd_repairs cra,
2224: rcv_transactions rcvt,
2225: oe_order_headers_all oeh,
2226: oe_order_lines_all oel,
2227: hr_all_organization_units haou
2228: WHERE cra.creation_date between p_from_date and p_to_date
2229: AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2230: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2251: AND paramn1 = rcvt.transaction_id)
2252:
2253: AND ((ced.QUANTITY_REQUIRED < -1
2254: AND oel.line_id in ( Select line_id
2255: from oe_order_lines_all oel1
2256: start with oel1.line_id = ced.order_line_id
2257: connect by prior oel1.line_id = oel1.split_from_line_id
2258: and oel1.shipped_quantity is not null
2259: and oel1.header_id = oeh.header_id))
2292: ced.inventory_item_id prod_txn_item_id
2293: FROM hr_all_organization_units haou,
2294: csd_repairs cra,
2295: oe_order_headers_all oeh,
2296: oe_order_lines_all oel,
2297: rcv_transactions rcvt,
2298: cs_estimate_details ced,
2299: csd_product_transactions cpt
2300: WHERE cpt.repair_line_id = p_repair_line_id
2324:
2325:
2326: AND ((ced.QUANTITY_REQUIRED < -1
2327: AND oel.line_id in ( Select line_id
2328: from oe_order_lines_all oel1
2329: start with oel1.line_id = ced.order_line_id
2330: connect by prior oel1.line_id = oel1.split_from_line_id
2331: and oel1.shipped_quantity is not null
2332: and oel1.header_id = oeh.header_id))
3142: oeh.order_number
3143: from csd_product_transactions cpt,
3144: csd_repairs cra,
3145: po_requisition_headers_all prh,
3146: oe_order_lines_all oel,
3147: oe_order_headers_all oeh
3148: where cpt.repair_line_id = cra.repair_line_id
3149: AND cpt.req_header_id = prh.requisition_header_id
3150: AND cpt.order_line_id = oel.line_id
3178: oeh.order_number
3179: from csd_product_transactions cpt,
3180: csd_repairs cra,
3181: po_requisition_headers_all prh,
3182: oe_order_lines_all oel,
3183: oe_order_headers_all oeh
3184: where cpt.repair_line_id = cra.repair_line_id
3185: AND cpt.req_header_id = prh.requisition_header_id
3186: AND cpt.order_line_id = oel.line_id
4556: oeh.order_number
4557: from csd_product_transactions cpt,
4558: csd_repairs cra,
4559: po_requisition_headers_all prh,
4560: oe_order_lines_all oel,
4561: oe_order_headers_all oeh
4562: where cpt.repair_line_id = cra.repair_line_id
4563: AND cpt.req_header_id = prh.requisition_header_id
4564: AND cpt.order_line_id = oel.line_id
4592: oeh.order_number
4593: from csd_product_transactions cpt,
4594: csd_repairs cra,
4595: po_requisition_headers_all prh,
4596: oe_order_lines_all oel,
4597: oe_order_headers_all oeh
4598: where cpt.repair_line_id = cra.repair_line_id
4599: AND cpt.req_header_id = prh.requisition_header_id
4600: AND cpt.order_line_id = oel.line_id
6186: wsh_serial_numbers dsn,--Added to fix 3801614
6187: --Changed to view from table, bug: 4341784
6188: wsh_delivery_assignments_v wda,
6189: wsh_new_deliveries wnd,
6190: oe_order_lines_all oel,
6191: hr_all_organization_units hao
6192: Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6193: AND cpt.estimate_detail_id = ced.estimate_detail_id
6194: AND dd.delivery_detail_id = wda.delivery_detail_id
6211: where crh.repair_line_id = cpt.repair_line_id
6212: and crh.paramn1 = dd.delivery_detail_id
6213: and event_code = 'PS')
6214: AND oel.line_id in ( Select line_id
6215: from oe_order_lines_all oel1
6216: start with oel1.line_id = ced.order_line_id
6217: connect by prior oel1.line_id = oel1.split_from_line_id
6218: and oel1.shipped_quantity is not null
6219: and oel1.header_id = oel.header_id);
6262: wsh_serial_numbers dsn,--Added to fix 3801614
6263: --Changed to view from table, bug: 4341784
6264: wsh_delivery_assignments_v wda,
6265: wsh_new_deliveries wnd,
6266: oe_order_lines_all oel,
6267: hr_all_organization_units hao
6268: Where cra.creation_date between p_from_date and p_to_date
6269: AND cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6270: AND cpt.estimate_detail_id = ced.estimate_detail_id
6288: where crh.repair_line_id = cpt.repair_line_id
6289: and crh.paramn1 = dd.delivery_detail_id
6290: and event_code = 'PS')
6291: AND oel.line_id in ( Select line_id
6292: from oe_order_lines_all oel1
6293: start with oel1.line_id = ced.order_line_id
6294: connect by prior oel1.line_id = oel1.split_from_line_id
6295: and oel1.shipped_quantity is not null
6296: and oel1.header_id = oel.header_id);
6346: wsh_serial_numbers dsn,--Added to fix 3801614
6347: --Changed to view from table, bug: 4341784
6348: wsh_delivery_assignments_v wda,
6349: wsh_new_deliveries wnd,
6350: oe_order_lines_all oel,
6351: hr_all_organization_units hao
6352: Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
6353: AND cpt.estimate_detail_id = ced.estimate_detail_id
6354: AND dd.delivery_detail_id = wda.delivery_detail_id
6368: and crh.paramn1 = dd.delivery_detail_id
6369: and event_code = 'PS')
6370: AND cpt.repair_line_id = p_repair_line_id
6371: AND oel.line_id in ( Select line_id
6372: from oe_order_lines_all oel1
6373: start with oel1.line_id = ced.order_line_id
6374: connect by prior oel1.line_id = oel1.split_from_line_id
6375: and oel1.shipped_quantity is not null
6376: and oel1.header_id = oel.header_id);
7032: prh.segment1 requisition_number,
7033: hao.name source_org_name,
7034: hao1.name destination_org_name,
7035: trl.txn_source_id
7036: from oe_order_lines_all oel,
7037: wsh_delivery_details dd,
7038: wsh_serial_numbers dsn,--Added to fix 3801614
7039: po_requisition_lines_all prl,
7040: po_requisition_headers_all prh,
7089: prh.segment1 requisition_number,
7090: hao.name source_org_name,
7091: hao1.name destination_org_name,
7092: trl.txn_source_id
7093: from oe_order_lines_all oel,
7094: wsh_delivery_details dd,
7095: wsh_serial_numbers dsn,--Added to fix 3801614
7096: po_requisition_lines_all prl,
7097: po_requisition_headers_all prh,
9355:
9356:
9357: CURSOR c_product_transaction_id(p_repair_line_id IN number) is
9358: SELECT c.product_transaction_id, a.booked_flag
9359: FROM oe_order_lines_all a,
9360: cs_estimate_details b,
9361: csd_product_transactions c
9362: WHERE a.line_id = b.order_line_id
9363: AND b.estimate_detail_id = c.estimate_detail_id
9416:
9417: CURSOR CANCELLED_ORDER_LINES(p_repair_line_id NUMBER) IS
9418: SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
9419: FROM oe_order_headers_all oeh,
9420: oe_order_lines_all oel,
9421: cs_estimate_details ced,
9422: csd_product_transactions cpt
9423: WHERE cpt.repair_line_id = p_repair_line_id
9424: AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
9437: -- will show as cancelled.
9438: /***
9439: AND ((ced.QUANTITY_REQUIRED < -1
9440: AND oel.line_id in ( Select line_id
9441: from oe_order_lines_all oel1
9442: start with oel1.line_id = ced.order_line_id
9443: connect by prior oel1.line_id = oel1.split_from_line_id
9444: and oel1.shipped_quantity is not null
9445: and oel1.header_id = oeh.header_id))