SELECT creation_date, round (order_rec_pwp) order_rec_pwp, round (despatch_quantity_pwp) despatch_quantity_pwp, round (pend_pwp) pend_pwp ,
round(order_rec_np) order_rec_np ,
round(despatch_quantity_np) despatch_quantity_np , round(pend_np) pend_np , round(order_rec) order_rec , round(desp_total) desp_total ,
round(bucket) bucket,
round (bucket1) bucket1 , round(bucket2) bucket2 , round(bucket3) bucket3, round(bucket4) bucket4 , round(bucket5) bucket5 ,
round(bucket6) bucket6 ,MONTHS ,YEASRS
FROM (SELECT order_rec_pwp, despatch_quantity_pwp, pend_pwp, order_rec_np,
despatch_quantity_np, pend_np, order_rec, desp_total, bucket,
creation_date, (bucket1 / order_rec_pwp * 100) bucket1,
(bucket2 / order_rec_pwp * 100) bucket2,
(bucket3 / order_rec_pwp * 100) bucket3,
(bucket4 / order_rec_pwp * 100) bucket4,
(bucket5 / order_rec_pwp * 100) bucket5,
(bucket6 / order_rec_pwp * 100) bucket6,MONTHS ,YEASRS
FROM (SELECT SUM (order_rec_pwp) order_rec_pwp,
SUM (despatch_quantity_pwp) despatch_quantity_pwp,
SUM (pend_pwp) pend_pwp,
SUM (order_rec_np) order_rec_np,
SUM (despatch_quantity_np) despatch_quantity_np,
SUM (pend_np) pend_np, SUM (order_rec) order_rec,
SUM (desp_total) desp_total, SUM (bucket) bucket,
creation_date, SUM (bucket1) bucket1,
SUM (bucket2) bucket2, SUM (bucket3) bucket3,
SUM (bucket4) bucket4, SUM (bucket5) bucket5,
SUM (bucket6) bucket6 ,MONTHS , YEASRS
FROM (SELECT order_rec_pwp, despatch_quantity_pwp,
pend_pwp, order_rec_np,
despatch_quantity_np, pend_np, order_rec,
desp_total, bucket,
TO_CHAR (creation_date,
'MON-YY'
) creation_date,CASE WHEN TO_CHAR(creation_date,'MM') = 01 THEN TO_NUMBER(1)
WHEN TO_CHAR(creation_date,'MM') = 02 THEN TO_NUMBER(2)
WHEN TO_CHAR(creation_date,'MM') = 03 THEN TO_NUMBER(3)
WHEN TO_CHAR(creation_date,'MM') = 04 THEN TO_NUMBER(4)
WHEN TO_CHAR(creation_date,'MM') = 05 THEN TO_NUMBER(5)
WHEN TO_CHAR(creation_date,'MM') = 06 THEN TO_NUMBER(6)
WHEN TO_CHAR(creation_date,'MM') = 07 THEN TO_NUMBER(7)
WHEN TO_CHAR(creation_date,'MM') = 08 THEN TO_NUMBER(8)
WHEN TO_CHAR(creation_date,'MM') = 09 THEN TO_NUMBER(9)
WHEN TO_CHAR(creation_date,'MM') = 10 THEN TO_NUMBER(10)
WHEN TO_CHAR(creation_date,'MM') = 11 THEN TO_NUMBER(11)
WHEN TO_CHAR(creation_date,'MM') = 12 THEN TO_NUMBER(12)
ELSE 0 END MONTHS , to_char(creation_date,'YY') YEASRS ,
(CASE
WHEN bucket >= 1 AND bucket <= 15
THEN despatch_quantity_pwp
ELSE 0
END
) bucket1,
(CASE
WHEN bucket >= 16 AND bucket <= 30
THEN despatch_quantity_pwp
ELSE 0
END
) bucket2,
(CASE
WHEN bucket >= 31 AND bucket <= 45
THEN despatch_quantity_pwp
ELSE 0
END
) bucket3,
(CASE
WHEN bucket >= 46 AND bucket <= 60
THEN despatch_quantity_pwp
ELSE 0
END
) bucket4,
(CASE
WHEN bucket >= 61 AND bucket <= 90
THEN despatch_quantity_pwp
ELSE 0
END
) bucket5,
(CASE
WHEN bucket >= 91
THEN despatch_quantity_pwp
ELSE 0
END
) bucket6,
order_number
FROM (SELECT order_rec_pwp,
despatch_quantity_pwp,
( order_rec_pwp
- despatch_quantity_pwp
) pend_pwp,
order_rec_np, despatch_quantity_np,
pend_np,
(order_rec_pwp + order_rec_np
) order_rec,
( despatch_quantity_pwp
+ despatch_quantity_np
) desp_total,
ABS
(( TO_DATE (ordered_date,
'DD-MON-YYYY'
)
- TO_DATE (issue_date,
'DD-MON-YYYY'
)
)
)
+ 1 AS bucket,
creation_date, order_number
FROM (SELECT (SELECT SUM
(CASE
WHEN ool.ordered_quantity IS NULL
THEN 0
ELSE ool.ordered_quantity
END
)
FROM apps.oe_order_lines_all ool
WHERE ool.header_id =
oola.header_id)
order_rec_pwp,
(SELECT SUM
(CASE
WHEN jow.quantity IS NULL
THEN 0
ELSE jow.quantity
END
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
despatch_quantity_pwp,
0 order_rec_np,
0 despatch_quantity_np,
0 pend_np,
ooha.order_type_id,
ooha.order_number,
ooha.ordered_date
ordered_date,
(SELECT MAX
(jow.creation_date
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
issue_date,
(SELECT MAX
(excise_invoice_date
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
excise_invoice,
ooha.creation_date
creation_date
FROM apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.jai_om_wsh_lines_all jowla,
apps.mtl_system_items_b msib,
apps.mtl_item_categories mic,
apps.mtl_categories mc,
apps.oe_transaction_types_tl ottt,
apps.fnd_lookup_values flv
WHERE
--ooha.order_number = '12013002388'
oola.flow_status_code NOT IN
('CANCELLED',
'ENTERED')
AND TRUNC (ooha.creation_date)
BETWEEN NVL
(:p_from_date,
ooha.creation_date
)
AND NVL
(:p_to_date,
ooha.creation_date
)
AND ooha.header_id =
oola.header_id
AND ooha.order_type_id =
jowla.order_type_id
AND ooha.header_id =
jowla.order_header_id
AND msib.organization_id =
oola.ship_from_org_id
AND msib.inventory_item_id =
oola.inventory_item_id
AND oola.inventory_item_id =
jowla.inventory_item_id
AND msib.organization_id =
mic.organization_id
AND msib.inventory_item_id =
mic.inventory_item_id
AND mic.category_set_id = 5
AND mc.category_id =
mic.category_id
AND msib.organization_id =
NVL
(:p_org,
msib.organization_id
)
AND mc.segment1 = 'PWP'
AND mc.segment1 =
NVL (:p_seg,
mc.segment1
)
AND ooha.order_type_id =
ottt.transaction_type_id
AND flv.lookup_code =
UPPER (ottt.NAME)
AND flv.tag =
NVL (:p_order_type,
flv.tag
)
GROUP BY ooha.order_type_id,
oola.header_id,
ooha.order_number,
ooha.creation_date,
ooha.ordered_date )))
GROUP BY creation_date ,YEASRS,MONTHS)
UNION
SELECT order_rec_np, despatch_quantity_np, pend_np, order_rec_pwp,
despatch_quantity_pwp, pend_pwp, order_rec, desp_total,
bucket, creation_date,
(bucket1 / order_rec_np * 100) bucket1,
(bucket2 / order_rec_np * 100) bucket2,
(bucket3 / order_rec_np * 100) bucket3,
(bucket4 / order_rec_np * 100) bucket4,
(bucket5 / order_rec_np * 100) bucket5,
(bucket6 / order_rec_np * 100) bucket6,MONTHS ,YEASRS
FROM (SELECT SUM (order_rec_np) order_rec_np,
SUM (despatch_quantity_np) despatch_quantity_np,
SUM (pend_np) pend_np,
SUM (order_rec_pwp) order_rec_pwp,
SUM (despatch_quantity_pwp) despatch_quantity_pwp,
SUM (pend_pwp) pend_pwp, SUM (order_rec) order_rec,
SUM (desp_total) desp_total, SUM (bucket) bucket,
creation_date, SUM (bucket1) bucket1,
SUM (bucket2) bucket2, SUM (bucket3) bucket3,
SUM (bucket4) bucket4, SUM (bucket5) bucket5,
SUM (bucket6) bucket6,MONTHS ,YEASRS
FROM (SELECT order_rec_np, despatch_quantity_np,
pend_np, order_rec_pwp,
despatch_quantity_pwp, pend_pwp, order_rec,
desp_total, bucket,
TO_CHAR (creation_date,
'MON-YY'
) creation_date,CASE WHEN TO_CHAR(creation_date,'MM') = 01 THEN TO_NUMBER(1)
WHEN TO_CHAR(creation_date,'MM') = 02 THEN TO_NUMBER(2)
WHEN TO_CHAR(creation_date,'MM') = 03 THEN TO_NUMBER(3)
WHEN TO_CHAR(creation_date,'MM') = 04 THEN TO_NUMBER(4)
WHEN TO_CHAR(creation_date,'MM') = 05 THEN TO_NUMBER(5)
WHEN TO_CHAR(creation_date,'MM') = 06 THEN TO_NUMBER(6)
WHEN TO_CHAR(creation_date,'MM') = 07 THEN TO_NUMBER(7)
WHEN TO_CHAR(creation_date,'MM') = 08 THEN TO_NUMBER(8)
WHEN TO_CHAR(creation_date,'MM') = 09 THEN TO_NUMBER(9)
WHEN TO_CHAR(creation_date,'MM') = 10 THEN TO_NUMBER(10)
WHEN TO_CHAR(creation_date,'MM') = 11 THEN TO_NUMBER(11)
WHEN TO_CHAR(creation_date,'MM') = 12 THEN TO_NUMBER(12)
ELSE 0 END MONTHS , to_char(creation_date,'YY') YEASRS ,
(CASE
WHEN bucket >= 1 AND bucket <= 15
THEN despatch_quantity_np
ELSE 0
END
) bucket1,
(CASE
WHEN bucket >= 16 AND bucket <= 30
THEN despatch_quantity_np
ELSE 0
END
) bucket2,
(CASE
WHEN bucket >= 31 AND bucket <= 45
THEN despatch_quantity_np
ELSE 0
END
) bucket3,
(CASE
WHEN bucket >= 46 AND bucket <= 60
THEN despatch_quantity_np
ELSE 0
END
) bucket4,
(CASE
WHEN bucket >= 61 AND bucket <= 90
THEN despatch_quantity_np
ELSE 0
END
) bucket5,
(CASE
WHEN bucket >= 91
THEN despatch_quantity_np
ELSE 0
END
) bucket6,
order_number
FROM (SELECT order_rec_np, despatch_quantity_np,
( order_rec_pwp
- despatch_quantity_pwp
) pend_np,
order_rec_pwp,
despatch_quantity_pwp, pend_pwp,
(order_rec_pwp + order_rec_np
) order_rec,
( despatch_quantity_pwp
+ despatch_quantity_np
) desp_total,
ABS
(( TO_DATE (ordered_date,
'DD-MON-YYYY'
)
- TO_DATE (issue_date,
'DD-MON-YYYY'
)
)
)
+ 1 AS bucket,
creation_date, order_number
FROM (SELECT (SELECT SUM
(CASE
WHEN ool.ordered_quantity IS NULL
THEN 0
ELSE ool.ordered_quantity
END
)
FROM apps.oe_order_lines_all ool
WHERE ool.header_id =
oola.header_id)
order_rec_np,
(SELECT SUM
(CASE
WHEN jow.quantity IS NULL
THEN 0
ELSE jow.quantity
END
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
despatch_quantity_np,
0 order_rec_pwp,
0 despatch_quantity_pwp,
0 pend_pwp,
ooha.order_type_id,
ooha.order_number,
ooha.ordered_date
ordered_date,
(SELECT MAX
(jow.creation_date
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
issue_date,
(SELECT MAX
(excise_invoice_date
)
FROM apps.jai_om_wsh_lines_all jow
WHERE oola.header_id =
jow.order_header_id)
excise_invoice,
ooha.creation_date
creation_date
FROM apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.jai_om_wsh_lines_all jowla,
apps.mtl_system_items_b msib,
apps.mtl_item_categories mic,
apps.mtl_categories mc,
apps.oe_transaction_types_tl ottt,
apps.fnd_lookup_values flv
WHERE
--ooha.order_number = '12013002388'
oola.flow_status_code NOT IN
('CANCELLED',
'ENTERED')
AND TRUNC (ooha.creation_date)
BETWEEN NVL
(:p_from_date,
ooha.creation_date
)
AND NVL
(:p_to_date,
ooha.creation_date
)
AND ooha.header_id =
oola.header_id
AND ooha.order_type_id =
jowla.order_type_id
AND ooha.header_id =
jowla.order_header_id
AND msib.organization_id =
oola.ship_from_org_id
AND msib.inventory_item_id =
oola.inventory_item_id
AND oola.inventory_item_id =
jowla.inventory_item_id
AND msib.organization_id =
mic.organization_id
AND msib.inventory_item_id =
mic.inventory_item_id
AND mic.category_set_id = 5
AND mc.category_id =
mic.category_id
AND msib.organization_id =
NVL
(:p_org,
msib.organization_id
)
AND mc.segment1 = 'NP'
AND mc.segment1 =
NVL (:p_seg,
mc.segment1
)
AND ooha.order_type_id =
ottt.transaction_type_id
AND flv.lookup_code =
UPPER (ottt.NAME)
AND flv.tag =
NVL (:p_order_type,
flv.tag
)
GROUP BY ooha.order_type_id,
oola.header_id,
ooha.order_number,
ooha.creation_date,
ooha.ordered_date
ORDER BY ooha.creation_date)))
GROUP BY creation_date ,MONTHS ,YEASRS
))
ORDER BY YEASRS ,MONTHS
No comments:
Post a Comment