Untitled
unknown
pgsql
a year ago
9.7 kB
8
Indexable
prodsakinah1agt2024=# SELECT fzi_migration_additional_purchase_good_receive_v2(
prodsakinah1agt2024(# MD5(NOW()::text),
prodsakinah1agt2024(# 10, -2,
prodsakinah1agt2024(# TO_CHAR(NOW(), 'YYYYMMDDHH24MISS'),
prodsakinah1agt2024(# '0001-01-01 00:00:00',
prodsakinah1agt2024(# '9999-01-01 00:00:00'
prodsakinah1agt2024(# );
ERROR: relation "invalid_master_partner_is_not_yet_migrated" does not exist
LINE 106: AND NOT EXISTS ( SELECT 1 FROM invalid_master_partner_is_...
^
QUERY: WITH tobe_updated_po_item as (
select D.po_item_id as po_item_id,
pTenantId as tenant_id,
sum(A.received) as updated_qty,
A.object_id as object_id,
A.transaction_id as procurement_id,
B.po_id as ref_id,
A.difference_final as difference_final,
A.transaction_id as doc_no,
C.product_id as product_id,
D.gross_price_po as gross_price_po,
SUM(A.received) as qty_po,
-- SUM(A.received) as qty_int,
public.fzi_get_qty_int_general(SUM(A.received),A.difference_package_final,1) as qty_int,
A.supplier_id as supplier_id
from zicare.object_received A
inner join public.pu_po B on B.doc_no = A.po_no
join public.m_product C on C.product_code = A.object_id
inner join public.pu_po_item D on D.po_id = B.po_id and D.product_id = C.product_id and D.gross_price_po = A.rate
where exists (
select 1
from zi_migration B
where B.data_type = vDataTypePO
and B.data_unique = A.po_no || '-' || public.fzi_get_partner_id_by_code(A.supplier_id,pTenantId)
)
and (
A.modified_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp OR A.created_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp
)
group by A.po_no,A.object_id,A.rate,A.transaction_id,D.po_item_id,C.product_id,B.po_id,
A.difference_final,D.gross_price_po,A.difference_package_final,A.supplier_id
having exists (
select 1
from zi_migration E
where E.data_type = vDataTypePOItem
-- and E.data_unique = concat_ws('.',A.po_no,C.product_id,A.rate)
AND E.data_unique = concat_ws('.',A.po_no || '-' || public.fzi_get_partner_id_by_code(A.supplier_id,pTenantId),
C.product_id,A.rate)
and E.data_change = SUM(A.received)::text
and E.flg_last = 'Y'
)
),
updated_po_item as (
UPDATE public.pu_po_item AS A
SET qty_po = B.updated_qty
FROM tobe_updated_po_item B
WHERE A.po_item_id = B.po_item_id
RETURNING A.*
),
updated_po_balance_item as (
UPDATE public.pu_po_balance_item AS A
SET qty_po = B.qty_po,
update_datetime = pDatetime,
update_user_id = pUserId,
"version" = A.version
FROM updated_po_item B
WHERE A.po_item_id = B.po_item_id
RETURNING A.*
),
inserted_new_goods_receive as (
INSERT INTO public.pu_receive_goods(
tenant_id,
doc_type_id,
doc_no,
doc_date,
ou_id,
ext_doc_no,
ext_doc_date,
ref_doc_type_id,
ref_id,
remark,
partner_id,
warehouse_id,
status_doc,
workflow_status,
version,
create_datetime,
create_user_id,
update_datetime,
update_user_id
)
SELECT pTenantId AS tenant_id,
-- A.doc_type_id AS doc_type_id,
CAST('111' as bigint) AS doc_type_id,
B.procurement_id AS doc_no,
TO_CHAR(B.procurement_date, 'YYYYMMDD') as doc_date,
cast('10' as bigint) as ou_id,
'-' as ext_doc_no,
TO_CHAR(B.procurement_date, 'YYYYMMDD') as ext_doc_date,
cast('101' as bigint) as ref_doc_type_id,
A.ref_id as ref_id,
coalesce(B.po_note,'') as remark,
C.partner_id as partner_id,
D.warehouse_id as warehouse_id,
'R' as status_doc,
'APPROVED' as workflow_status,
'0' as "version",
pDatetime AS create_datetime,
pUserId AS create_user_id,
pDatetime AS update_datetime,
pUserId as update_user_id
FROM tobe_updated_po_item A
join zicare.object_procurement B on B.procurement_id = A.procurement_id
join public.m_partner C on C.partner_code = B.supplier_id
join public.m_warehouse D on D.warehouse_code = B.depo_id and D.tenant_id = pTenantId
WHERE TRUE
AND NOT EXISTS ( SELECT 1 FROM invalid_master_partner_is_not_yet_migrated E WHERE A.process_no = E.data_unique )
AND NOT EXISTS ( SELECT 1 FROM invalid_master_warehouse_is_not_yet_migrated F WHERE A.process_no = F.data_unique )
AND NOT EXISTS ( SELECT 1 FROM invalid_master_product_is_not_yet_migrated G WHERE A.process_no = G.data_unique )
AND NOT EXISTS ( SELECT 1 FROM invalid_master_product_not_set_coa H WHERE A.process_no = H.data_unique )
AND NOT EXISTS ( SELECT 1 FROM validate_quantity_is_less_than_zero I WHERE A.process_no = I.data_unique )
group by B.procurement_id,B.procurement_date,A.ref_id,C.partner_id,D.warehouse_id,
B.po_id,B.po_note
RETURNING public.pu_receive_goods.*
),
inserted_new_goods_receive_item as (
INSERT INTO public.pu_receive_goods_item(
tenant_id,
receive_goods_id,
line_no,
ref_doc_type_id,
ref_id,
product_id,
qty_rcv_po,
po_uom_id,
qty_rcv_int,
base_uom_id,
remark,
version,
create_datetime,
create_user_id,
update_datetime,
update_user_id
)
SELECT pTenantId AS tenant_id,
B.receive_goods_id AS receive_goods_id,
ROW_NUMBER() OVER(PARTITION BY A.doc_no ORDER BY A.object_id) as line_no,
B.ref_doc_type_id as ref_doc_type_id,
-- cast('-99' as bigint) as ref_id,
F.po_item_id as ref_id,
C.product_id as product_id,
SUM(A.qty_po) as qty_rcv_po,
D.uom_id as po_uom_id,
-- SUM(A.qty_int) as qty_rcv_int,
A.qty_int as qty_rcv_int,
-- D.uom_id as base_uom_id,
C.base_uom_id as base_uom_id,
'' as remark,
'0' AS "version",
pDatetime AS create_datetime,
pUserId AS create_user_id,
pDatetime AS update_datetime,
pUserId as update_user_id
FROM tobe_updated_po_item A
join inserted_new_goods_receive B on B.doc_no = A.procurement_id
join public.m_product C on C.product_code = A.object_id
join public.m_uom D on D.uom_code = A.difference_final and D.tenant_id = pTenantId
join public.pu_po E on E.doc_no = coalesce(A.doc_no,A.procurement_id) and E.tenant_id = pTenantId
join public.pu_po_item F on F.po_id = E.po_id and F.product_id = A.product_id and F.gross_price_po = A.gross_price_po
group by B,receive_goods_id, B.doc_no, B.ref_doc_type_id, A.ref_id,
B.warehouse_id, B.partner_id, C.product_id,D.uom_id, B.doc_date,
A.product_id, F.po_item_id,A.doc_no,A.object_id
order by A.doc_no,A.object_id
returning *
),
new_execute_approval_by_role_and_process as(
SELECT public.fzi_insert_approval_by_role(
A.tenant_id,
A.create_user_id,
-99,
A.create_datetime,
A.receive_goods_id,
A.doc_type_id,
A.doc_no,
A.doc_date,
A.remark
),
public.fzi_insert_process_message(
A.tenant_id,
A.create_user_id,
A.create_datetime,
A.receive_goods_id,
A.doc_no,
'pu_submit_receive_goods',
'ReceiveGoodsLineQueue',
'receiveGoodsId'
)
FROM inserted_new_goods_receive A
),
inserted_zi_migration_additional_po_item as (
INSERT INTO zi_migration(
id_data,
session_id,
data_type,
data_unique,
migration_type,
data_change,
flg_last,
remark,
create_user_id,
create_datetime,
update_user_id,
update_datetime,
"version"
)
SELECT nextval('zi_migration_seq'::regclass),
pSessionId,
vDataTypePOItem,
concat_ws('.',A.doc_no,A.product_id,A.gross_price_po) as data_unique,
'ADD',
A.qty_po as data_change,
'Y',
'',
pUserId,
pDatetime,
pUserId,
pDatetime,
0
FROM tobe_updated_po_item A
returning *
),
inserted_zi_migration_additional_goods_receive as (
INSERT INTO zi_migration(
id_data,
session_id,
data_type,
data_unique,
migration_type,
data_change,
flg_last,
remark,
create_user_id,
create_datetime,
update_user_id,
update_datetime,
"version"
)
SELECT nextval('zi_migration_seq'::regclass),
pSessionId,
VDataTypeGoodsReceive,
A.doc_no,
'ADD',
A.doc_no as data_change,
'Y',
'',
pUserId,
pDatetime,
pUserId,
pDatetime,
0
FROM inserted_new_goods_receive A
returning *
),
updated_existing_zi_migration as (
update zi_migration as A
set flg_last = 'N'
from inserted_zi_migration_additional_po_item B
where B.data_type = vDataTypePOItem
and A.data_unique = B.data_unique
and A.id_data != B.id_data
)
SELECT (SELECT COUNT(1) FROM inserted_new_goods_receive) AS inserted_new_goods_receive,
(SELECT COUNT(1) FROM inserted_new_goods_receive_item) AS inserted_new_goods_receive_item,
(SELECT COUNT(1) FROM tobe_updated_po_item) AS tobe_updated_po_item,
(SELECT COUNT(1) FROM updated_po_item) as updated_po_item,
(SELECT COUNT(1) FROM new_execute_approval_by_role_and_process) AS new_execute_approval_by_role_and_process,
(SELECT COUNT(1) FROM inserted_zi_migration_additional_po_item) AS inserted_zi_migration_additional_po_item,
(SELECT COUNT(1) FROM inserted_zi_migration_additional_goods_receive) AS inserted_zi_migration_additional_goods_receive
CONTEXT: PL/pgSQL function fzi_migration_additional_purchase_good_receive_v2(character varying,bigint,bigint,character varying,character varying,character varying) line 486 at SQL statement
prodsakinah1agt2024=#
Editor is loading...
Leave a Comment