Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION sl_cart_add_item(bigint, character varying, bigint, bigint, bigint, bigint)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pTenantId alias for $1;
- pDateTime alias for $2;
- pUserId alias for $3;
- pPartnerId alias for $4;
- pProductId alias for $5;
- pQty alias for $6;
- -- pStockPointCode alias for $7;
- vYearMonth character varying(6);
- vDefaultOuId bigint := -99;
- vStatusDraft character varying(1);
- vStateDraft character varying;
- vSlCartId bigint;
- vSlCartItemId bigint;
- vSlCartItemLog bigint;
- vSlCartDocNo character varying(30);
- vSlCartDocScheme character varying(10);
- vProductId bigint;
- vMemberId bigint;
- vStockPointId bigint;
- vCartTypeDs character varying;
- vCartTypeMember character varying;
- vFlgDs character varying;
- vEvent character varying;
- vRoundingMode character varying;
- vDecimalRounding integer;
- vGrossPrice numeric := 0;
- vDiscMemberPercent numeric := 0;
- vDiscMemberAmount numeric := 0;
- vDiscPromoPercent numeric := 0;
- vDiscPromoAmount numeric := 0;
- vSubTotalPriceAfterDisc numeric;
- vDsCommission numeric := 0;
- vFlowSoMlmId bigint;
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vSpaceValue character varying(6);
- vFlagRole character varying(1);
- vReqId character varying(50);
- vUserWebDS bigint;
- vUserSms bigint;
- vUserMobile bigint;
- vDocNo1 character varying(10);
- vDocNo2 character varying(10);
- vDocNoSo character varying(10);
- vDocNoPo character varying(10);
- pRefHeader REFCURSOR := 'refHeader';
- BEGIN
- vUserWebDS := -3;
- vUserSms := -2;
- vUserMobile := -4;
- vDecimalRounding := 0;
- vSubTotalPriceAfterDisc := 0;
- vStatusDraft := 'D';
- vStateDraft := 'DRAFT';
- vRoundingMode := 'RD';
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vSpaceValue := ' ';
- vYearMonth := ' ';
- vCartTypeDs := 'DS';
- vcartTypeMember := 'MEMBER';
- vEvent := 'MEMBER.ADD';
- --vDefaultOuId := 14; diambil dari system config
- -- SELECT f_get_value_system_config_by_param_code(pTenantId, 'DEFAULT.OU.ID') INTO vDefaultOuId;
- -- SELECT f_get_product_by_product_catalog_id(pProductCatalogId) INTO vProductId;
- -- cari partner_id
- -- SELECT B.partner_id INTO pPartnerId FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pStockPointCode;
- -- cari member_id ds berdasarkan pStockPointCode
- -- SELECT B.member_id INTO vStockPointId FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pStockPointCode;
- -- untuk mendapatkan member_Id ds maupun member
- -- SELECT member_id INTO vMemberId FROM mlm_member WHERE member_code = pMemberCode;
- /* --untuk mendapatkan flgDs
- IF EXISTS( SELECT 1 FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pMemberCode )
- THEN vFlgDs := vFlagYes;
- ELSE vFlgDs := vFlagNo;
- END IF; */
- -- gross price
- -- Harga katalog didapat via function
- -- SELECT f_get_catalog_price_by_partner(pPartnerId,pProductCatalogId) INTO vGrossPrice;
- -- disc member percent
- -- SELECT disc_member_percent INTO vDiscMemberPercent from m_product_catalog WHERE product_catalog_id = pProductCatalogId LIMIT 1;
- -- disc member percent
- -- SELECT disc_promo_percent INTO vDiscPromoPercent from m_product_catalog WHERE product_catalog_id = pProductCatalogId LIMIT 1;
- -- SELECT f_get_value_system_config_by_param_code(pTenantId, 'dsCommision')::numeric INTO vDsCommission;
- -- TODO:
- -- Samakan dengan model item
- -- vDiscMemberAmount := vGrossPrice * vDiscMemberPercent * 0.01;
- -- SELECT f_calc_amount_after_disc(vGrossPrice, vDiscMemberPercent, 'RD', vDecimalRounding) INTO vDiscMemberAmount;
- -- vDiscPromoAmount := vGrossPrice * vDiscPromoPercent * 0.01;
- -- SELECT f_calc_amount_after_disc(vGrossPrice - vDiscMemberAmount, vDiscPromoPercent, 'RD', vDecimalRounding) INTO vDiscPromoAmount;
- -- Check apakah ada CART yang masih DRAFT untuk pPartnerId bersangkutan
- IF EXISTS( SELECT 1 FROM sl_cart WHERE status_doc = vStatusDraft AND partner_id = pPartnerId)
- THEN
- -- ada yang draft, ambil sl cart id nya
- SELECT sl_cart_id INTO vSlCartId FROM sl_cart
- WHERE status_doc = vStatusDraft AND partner_id = pPartnerId
- ORDER BY sl_cart_id DESC
- LIMIT 1;
- -- UPDATE sl_cart SET update_user_id, update_datetime, version
- UPDATE sl_cart SET update_user_id = pUserId, update_datetime = pDateTime, version = version + 1 WHERE sl_cart_id = vSlCartId;
- ELSE
- vSlCartId := nextval('sl_cart_seq');
- -- SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.SO.PALOMAWEB') INTO vDocNoSo;
- -- SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.PO.PALOMAWEB') INTO vDocNoPo;
- SELECT substring(current_date::text from 3 for 2)||substring(current_date::text from 6 for 2)||'/' INTO vDocNo1;
- SELECT lpad(nextval('sl_cart_seq')::text, 6, '0') INTO vDocNo2;
- /* --insert ke sl_cart_commission
- INSERT INTO sl_cart_commission(
- sl_cart_id, commission_percentage, create_datetime, create_user_id,
- update_datetime, update_user_id, version)
- VALUES (vSlCartId,vDsCommission,pDateTime, pUserId,
- pDateTime, pUserId, 0); */
- -- Tidak ada yang draft, buat header
- INSERT INTO sl_cart(
- sl_cart_id, tenant_id, ou_id, partner_id,
- doc_no, doc_date, remark, status_doc,
- create_user_id, create_datetime, update_user_id,
- update_datetime, version)
- SELECT
- vSlCartId, pTenantId, vDefaultOuId, pPartnerId,
- vDocNo1||vDocNo2, substring(pDateTime,1,8) as doc_date, '', vStatusDraft,
- pUserId, pDateTime, pUserId, pDateTime, 0
- FROM m_partner A
- WHERE A.partner_id = pPartnerId;
- END IF;
- IF EXISTS( SELECT 1 FROM sl_cart_item WHERE sl_cart_id = vSlCartId AND product_id = pProductId )
- THEN
- UPDATE sl_cart_item Z
- SET qty = Z.qty +pQty,
- update_datetime = pDateTime,
- update_user_id = pUserId,
- version = Z.version+1
- WHERE Z.sl_cart_id = vSlCartId
- AND Z.product_id = pProductId;
- SELECT sl_cart_item_id INTO vSlCartItemId
- FROM sl_cart_item
- WHERE sl_cart_id = vSlCartId
- AND product_id =pProductId;
- ELSE
- vSlCartItemId :=nextval('sl_cart_item_seq');
- -- insert ke sl_cart_item
- INSERT INTO sl_cart_item(
- sl_cart_item_id,sl_cart_id, product_id, qty, catalog_price,
- discount_member,discount_member_amount, discount_promo,discount_promo_amount, price_after_discount,
- so_mlm_id, so_mlm_item_id, create_user_id, create_datetime, update_user_id,
- update_datetime, version)
- VALUES (vSlCartItemId,vSlCartId, pProductId, pQty, vGrossPrice,
- vDiscMemberPercent,vDiscMemberAmount,vDiscPromoPercent,vDiscPromoAmount, f_calc_price_after_disc(vGrossPrice, vDiscMemberPercent, vDiscPromoPercent),
- -99, -99, pUserId, pDateTime, pUserId,
- pDateTime, 0);
- END IF;
- -- Update nilai nett sell price
- UPDATE sl_cart_item SET total_price_after_disc = COALESCE(qty * price_after_discount,0)
- WHERE sl_cart_id = vSlCartId;
- -- Update sl_cart_item
- WITH item AS (
- SELECT A.sl_cart_id,SUM(B.total_price_after_disc) AS totalPrice,
- SUM(B.catalog_price * B.qty) AS totalHargaKatalog,
- SUM(B.discount_member_amount * B.qty) AS totalDiscMemberAmount,
- SUM(B.discount_promo_amount * B.qty) AS totalDiscPromoAmount
- FROM sl_cart A
- JOIN sl_cart_item B ON A.sl_cart_id = B.sl_cart_id
- WHERE A.sl_Cart_id = vSlCartId
- GROUP BY a.sl_cart_id
- )
- UPDATE sl_cart A
- SET total_ordered_amount = B.totalPrice,
- total_ordered_harga_katalog = B.totalHargaKatalog,
- total_ordered_discount_member_amount = B.totalDiscMemberAmount,
- total_ordered_discount_promo_amount = B.totalDiscPromoAmount,
- update_datetime = pDateTime,update_user_id = pUserId, version = A.version+1
- FROM item B
- WHERE A.sl_cart_id = B.sl_cart_id AND A.sl_cart_id = vSlCartId;
- vSlCartItemLog := nextval('sl_cart_item_log_seq');
- -- Insert into sl_cart_item_log
- INSERT INTO sl_cart_item_log(
- sl_cart_item_log_id, sl_cart_item_id, qty, event, remark, version,
- create_user_id, create_datetime, update_user_id, update_datetime)
- SELECT vSlCartItemLog,vSlCartItemId, pQty, vEvent, 'Produk ' || B.product_code || ' berhasil ditambahkan sebanyak ' || pQty :: varchar || ' oleh supplier.',0,
- pUserId,pDateTime,pUserId,pDateTime
- FROM sl_cart_item A
- INNER JOIN m_product B ON A.product_id = B.product_id
- WHERE A.sl_cart_item_id = vSlCartItemId;
- -- Data yang akan ditampilkan sebagai result
- Open pRefHeader FOR
- SELECT
- A.sl_cart_id, B.sl_cart_item_id, A.doc_no, A.doc_date, C.partner_id,
- D.product_id, D.product_code, D.product_name, B.qty, B.catalog_price,
- B.discount_member, B.discount_promo,B.price_after_discount, B.total_price_after_disc, B.so_mlm_id, B.so_mlm_item_id,
- B.discount_member_amount,B.discount_promo_amount,(B.catalog_price*B.qty) AS total_price,B.version
- FROM sl_cart A
- INNER JOIN sl_cart_item B ON A.sl_cart_id = B.sl_cart_id
- INNER JOIN m_partner C ON C.partner_id = A.partner_id
- INNER JOIN m_product D ON D.product_id = B.product_id
- WHERE A.sl_cart_id = vSlCartId
- AND B.sl_cart_item_id = vSlCartItemId;
- RETURN NEXT pRefHeader;
- --ELSE
- -- Open pRefHeader FOR
- --
- -- SELECT
- -- -99 AS sl_cart_id, -99 AS sl_cart_item_id, ' ' AS doc_no, ' ' AS doc_date, '' AS member_code,
- -- -99 AS product_catalog_id, ' ' AS product_catalog_code, ' ' AS product_catalog_name, 0 AS qty, 0 AS catalog_price,
- -- 0 AS discount_member, 0 AS discount_promo, 0 AS price_after_discount, 0 AS total_price_after_discount, -99 AS so_mlm_id, -99 AS so_mlm_item_id
- --
- -- RETURN NEXT pRefHeader;
- --END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment